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!

Want some clarity over Pivoting (Row to Column Conversion)

Discussion in 'SQL PL/SQL' started by laxman, Jul 8, 2009.

  1. laxman

    laxman Forum Expert

    Messages:
    144
    Likes Received:
    0
    Trophy Points:
    230
    Location:
    MUMBAI
    Kind attn: Rajuvu sir
    Dear sir,
    I have gone through your article Titled as Pivoting (Row to Column Conversion),it is extremely good to know the concept but i am facing some doubt to understand this code below which you have mentioned in your article.
    here in this code line no 10 ie 10 row_number() OVER ( partition by deptno order by rownum) rn
    how it is actually working here in this code.kindly give me some time to get this logic.
    also using Sys_connect_by_path how this code will work.
    It is something like feeding child with spoon,so please don't hesitate to explain it in brief.
    Code (Text):
    SQL> SELECT  deptno,
      2          MAX(DECODE ( rn , 1, ename )) EMP1,
      3          MAX(DECODE ( rn , 2, ename )) EMP2,
      4          MAX(DECODE ( rn , 3, ename )) EMP3,
      5          MAX(DECODE ( rn , 4, ename )) EMP4,
      6          MAX(DECODE ( rn , 5, ename )) EMP5,
      7          MAX(DECODE ( rn , 6, ename )) EMP6
      8  FROM
      9  (SELECT  empno , ename, deptno,
     [COLOR="red"]10           row_number() OVER ( partition by deptno  order by rownum) rn[/COLOR]
     11  FROM  EMP)
     12  GROUP BY deptno;

        DEPTNO EMP1       EMP2       EMP3       EMP4       EMP5       EMP6
    ---------- ---------- ---------- ---------- ---------- ---------- ----------
            10 CLARK      KING       MILLER
            20 SMITH      JONES      SCOTT      ADAMS      FORD
            30 ALLEN      WARD       MARTIN     BLAKE      TURNER     JAMES
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    row_number() OVER ( partition by deptno order by rownum)
    Here returns the rank on Emp table based on column value deptno (A kind of group by) and order by. Ie, if table have 5 records of Dept10 and 3 records with Dept20 , this fucnctgion gives sequences from 1-5 for Dept10 and 1-3 for Det20 (Because Pratition by clause is on Deptno).

    Following links will give you the 'spoon feed' you are looking for ... :)

    SYS_CONNECT_BY_PATH
    Analytic Functions
     
  3. laxman

    laxman Forum Expert

    Messages:
    144
    Likes Received:
    0
    Trophy Points:
    230
    Location:
    MUMBAI
    Dear Sir,
    Now i got the whole concept.

    Thanks a lot.

    Laxman:)