14 Replies Latest reply: Apr 1, 2013 7:23 AM by newbie RSS

    WANT QUERY

    newbie
      Can you please help me to get the output
      MONTH_ID     GROUP_DESC     COB_CD     PROJ_TRX     
      200901     AAA     A     100
      200901     AAA     C     200
      200901     AAA     N     300
      200901     AAA     S     400
      200902     AAA     A     500
      200902     AAA     C     600
      200902     AAA     N     700
      200902     AAA     S     800
      
      REQUIRED OUTPUT
      
      GROUP_DESC,COB_CD,200901,200902
      AAA,A,100,500
      AAA,C,200,600
      AAA,N,300,700
      AAA,S,400,800
        • 1. Re: WANT QUERY
          O.Developer
          newbie wrote:
          Can you please help me to get the output
          MONTH_ID     GROUP_DESC     COB_CD     PROJ_TRX     
          200901     AAA     A     100
          200901     AAA     C     200
          200901     AAA     N     300
          200901     AAA     S     400
          200902     AAA     A     500
          200902     AAA     C     600
          200902     AAA     N     700
          200902     AAA     S     800
          
          REQUIRED OUTPUT
          
          GROUP_DESC,COB_CD,200901,200902
          AAA,A,100,500
          AAA,C,200,600
          AAA,N,300,700
          AAA,S,400,800
          -------

          HI

          Your requirement is to get multiple rows values into single row ..

          First of all, please provide your environment details , such as DB Ver, OS .

          I can give example form Scott Schema, if you want list all employee name for a dept in single line,try below- and try same in your table case with DESC
          select
          deptno,
          rtrim (xmlagg (xmlelement (e, ename || ',')).extract ('//text()'), ',') enames
          from
          emp
          group by
          deptno
          • 2. Re: WANT QUERY
            1000040
            newbie wrote:
            Can you please help me to get the output
            MONTH_ID     GROUP_DESC     COB_CD     PROJ_TRX     
            200901     AAA     A     100
            200901     AAA     C     200
            200901     AAA     N     300
            200901     AAA     S     400
            200902     AAA     A     500
            200902     AAA     C     600
            200902     AAA     N     700
            200902     AAA     S     800
            
            REQUIRED OUTPUT
            
            GROUP_DESC,COB_CD,200901,200902
            AAA A 100 500
            AAA C 200 600
            AAA N 300 700
            AAA S 400 800
            @O.Developer,

            my query output is changed...sorry for confusion'..corrected, please check
            • 3. Re: WANT QUERY
              sb92075
              are you & newbie doing the same homework assignment?
              • 4. Re: WANT QUERY
                1000040
                Sir ,

                Apologies...actually ..i have taken results from excel.which i cant paste results here, i thought my a/c locked .Thats why i posted from my another account.
                • 5. Re: WANT QUERY
                  1000040
                  Sir,

                  Can anyone help me please
                  • 6. Re: WANT QUERY
                    1000040
                    Anybody,

                    can you please help me
                    • 7. Re: WANT QUERY
                      sb92075
                      How do I ask a question on the forums?
                      SQL and PL/SQL FAQ
                      • 8. Re: WANT QUERY
                        1000040
                        sir,

                        i gave sample output please help me
                        • 9. Re: WANT QUERY
                          O.Developer
                          997037 wrote:
                          newbie wrote:
                          Can you please help me to get the output
                          MONTH_ID     GROUP_DESC     COB_CD     PROJ_TRX     
                          200901     AAA     A     100
                          200901     AAA     C     200
                          200901     AAA     N     300
                          200901     AAA     S     400
                          200902     AAA     A     500
                          200902     AAA     C     600
                          200902     AAA     N     700
                          200902     AAA     S     800
                          
                          REQUIRED OUTPUT
                          
                          GROUP_DESC,COB_CD,200901,200902
                          AAA A 100 500
                          AAA C 200 600
                          AAA N 300 700
                          AAA S 400 800
                          @O.Developer,

                          my query output is changed...sorry for confusion'..corrected, please check
                          --------------------------
                          Give your output format properly without overlap of any filed !
                          • 10. Re: WANT QUERY
                            S10390
                            Check this out..

                            select group_desc,
                            cob_cd,
                            sum(decode(monthid,200901,proj_trx,0)) as "200901" ,
                            sum(decode(monthid,200902,proj_trx,0)) as "200902"
                            from test_table
                            group by group_desc, cob_cd
                            order by 3 , 4;
                            • 11. Re: WANT QUERY
                              surendra4y
                              use thia one also

                              select group_desc,
                              cob_cd,
                              SUM(CASE WHEN MONTH_ID=200901 THEN proj_trx ELSE 0 END ) as "200901" ,
                              SUM(CASE WHEN MONTH_ID=200902 THEN proj_trx ELSE 0 END ) as "200902"
                              from testing2
                              group by group_desc, cob_cd
                              • 12. Re: WANT QUERY
                                newbie
                                997176 wrote:
                                use thia one also

                                select group_desc,
                                cob_cd,
                                SUM(CASE WHEN MONTH_ID=200901 THEN proj_trx ELSE 0 END ) as "200901" ,
                                SUM(CASE WHEN MONTH_ID=200902 THEN proj_trx ELSE 0 END ) as "200902"
                                from testing2
                                group by group_desc, cob_cd
                                Many thanks for the solution.

                                here am getting the results like this
                                200901
                                100
                                200
                                300
                                400

                                How can i get the values like below
                                200901
                                1000<total>
                                100
                                200
                                300
                                400
                                • 13. Re: WANT QUERY
                                  Manik
                                  Something like this ???

                                  UNTESTED!
                                  SELECT *
                                    FROM (  SELECT group_desc,
                                                   CASE
                                                      WHEN group_desc IS NOT NULL AND cob_cd IS NULL THEN 'TOTAL'
                                                      ELSE cob_Cd
                                                   END,
                                                   SUM (CASE WHEN MONTH_ID = 200901 THEN proj_trx ELSE 0 END)
                                                      AS "200901",
                                                   SUM (CASE WHEN MONTH_ID = 200902 THEN proj_trx ELSE 0 END)
                                                      AS "200902"
                                              FROM testing2
                                          GROUP BY ROLLUP (group_desc, cob_cd))
                                   WHERE group_desc IS NOT NULL;
                                  Cheers,
                                  Manik.
                                  • 14. Re: WANT QUERY
                                    newbie
                                    Thank you sir