1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

unpivot(column to row)

Discussion in 'SQL PL/SQL' started by krithika@2001, Aug 27, 2009.

  1. krithika@2001

    krithika@2001 Active Member

    Messages:
    30
    Likes Received:
    0
    Trophy Points:
    80
    Hi
    I am facing a similar situation


    SELECT distinct NS00_POLICY_NBR, DECODE ( l, 1, 'NS00_MEDICARE_A' ,
    DECODE(l, 2, 'NS00_MEDICARE_B',
    DECODE(l, 3, 'NS00_MEDIGAP',
    DECODE(1, 4,'NS00_INPAT',
    DECODE(1, 5,'NS00_OUTPAT',
    DECODE(1, 6,'NS00_PREG',
    DECODE(1, 7,'NS00_PHARMACY',
    DECODE(1, 8,'NS00_DENTAL',
    DECODE(1, 9,'NS00_MENTAL',
    DECODE(1, 10,'NS00_CANCER',
    DECODE(1, 11,'NS00_ACCIDENT',
    DECODE(1, 12,'NS00_LTC',
    DECODE(1, 13,'NS00_OTHER')))))))))))))COVERAGE,


    DECODE ( l, 1, NS00_MEDICARE_A ,
    DECODE(l, 2, NS00_MEDICARE_B,
    DECODE(l, 3, NS00_MEDIGAP,
    DECODE(1, 4,NS00_INPAT,
    DECODE(1, 5,NS00_OUTPAT,
    DECODE(1, 6,NS00_PREG,
    DECODE(1, 7,NS00_PHARMACY,
    DECODE(1, 8,NS00_DENTAL,
    DECODE(1, 9,NS00_MENTAL,
    DECODE(1,10,NS00_CANCER,
    DECODE(1,11,NS00_ACCIDENT,
    DECODE(1,12,NS00_LTC,
    DECODE(1,13,NS00_OTHER))))))))))))) VALUE



    FROM
    TPL_INSU_SW75IT06 ,
    (SELECT level l FROM DUAL X CONNECT BY LEVEL <=13) DMY
    Order by 1 ,2;


    But this is not working fine.I am getting only medicaid,medicare and medigap values
    and not the other values.Please let me know what to do.it s very urgent

    Thanks and Regards
    Krithika Swaminathan
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Sorry, Can't make any comment unless u provide Actual or test data. All depends on the data in the table.
     
  3. krithika@2001

    krithika@2001 Active Member

    Messages:
    30
    Likes Received:
    0
    Trophy Points:
    80
    Hi rajavu


    Thanks a ton for your article on pivoting.It was very useful for my work.


    Regards
    Krithika