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
    SQL> SELECT deptno,
    2 DECODE ( l, 1, 'ANALYST' ,
    3 DECODE(l, 2, 'CLERK',
    4 DECODE(l, 3, 'MANAGER',
    5 DECODE(l, 4, 'PRESIDENT',
    6 DECODE(l, 5, 'SALESMAN' ))))) JOB ,
    7 DECODE ( l, 1, ANALYST ,
    8 DECODE(l, 2, CLERK,
    9 DECODE(l, 3, MANAGER,
    10 DECODE(l, 4, PRESIDENT,
    11 DECODE(l, 5, SALESMAN ))))) SAL
    12 FROM
    13 EMP_JOB_PIVOT ,
    14 (SELECT level l FROM DUAL X CONNECT BY LEVEL <=5) DMY
    15 Order by 1 ,2;

    Can you please explain this code to me rajavu sir plzzzz


    Thanks and Regards
    Krithika Swaminathan
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Here Unpivoting is done against the already Pivoted table. EMP_JOB_PIVOT is a pivoted table based on Salary. This Table is joined with inline view (SELECT level l FROM DUAL X CONNECT BY LEVEL <=5) to produce a cartesian product. Now each row in EMP_JOB_PIVOT is replicated five times. Depending on that repetition index ( 'l' in the query) , The fields are decoded.

    In order to make it more clear, Try

    Code (SQL):

    SELECT deptno, l
     FROM
     EMP_JOB_PIVOT ,
     (SELECT level l FROM DUAL X CONNECT BY LEVEL <=5) DMY
     ORDER BY 1 ,2;
    Then it will be more clear how its working.