This discussion is archived
14 Replies Latest reply: Apr 1, 2013 5:23 AM by newbie RSS

WANT QUERY

newbie Explorer
Currently Being Moderated
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 Journeyer
    Currently Being Moderated
    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
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    are you & newbie doing the same homework assignment?
  • 4. Re: WANT QUERY
    1000040 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Sir,

    Can anyone help me please
  • 6. Re: WANT QUERY
    1000040 Newbie
    Currently Being Moderated
    Anybody,

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

    i gave sample output please help me
  • 9. Re: WANT QUERY
    O.Developer Journeyer
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    Thank you sir