Pivot with Connect_By

This is a different version of the pivot that I hadn't seen. I wanted to be able to find it so I listed it here. The original code can be found in an article by Younes Naguib.

Column Name_List Format A100

Select Deptno, 
              -- use the substring to get rid of the initial comma
              ----------------------------------------------------
              Substr(Sys_Connect_By_Path(Ename, ','),2)
              As
       Name_List
  From
      (
       Select Ename,
              Deptno,
              --------------------------------------------------
                    Count(*) Over ( Partition By Deptno )
                    As
              Cnt,
              --------------------------------------------------
                    Row_Number () Over ( Partition By Deptno
                                             Order By Ename  )
                    As
              Seq
         From Emp
      )
 Where Seq = Cnt  -- only pick up the first one
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
      Start With  Seq     = 1
 Connect By Prior Seq + 1 = Seq
        And Prior Deptno  = Deptno; 

(TinyURL: http://tinyurl.com/sql-pivot)

PL/SQL Loop Creates Pivot

Declare
   All_Depts Varchar2 (50) := '';

   Cursor c_Loc Is
                    -----------------------------------
                    -- don't put ',' in front of 1st
                    -----------------------------------
      Select        Case When Rownum <> 1 Then ',' End 
                    || 
                    Loc 
                    As 
             Loc
        From Dept;

Begin
   For c_Each In c_Loc
   Loop
      All_Depts := All_Depts || c_Each.Loc;
   End Loop;

   Dbms_Output.Put_Line (All_Depts);
End;
/

Pivot (9i)

This code will take data normally shown in columns and move it into rows. The Row_Number statement gives each ENAME an order number broken (or partitioned) into departments. The top-level query then takes the maximum value of each (which would be the name) and condenses it onto one line using the Group By.

The obvious thing to watch for here is you have to know how many possible items might need to be on a row, and code a "Max(Case" line for each.

(This is how you do this in 9i. I believe I've heard there is a new function in 10g -- I'll update this when I find it.)

   Select Deptno,
          Max(Case When Ord = 1 Then Ename Else '' End) Ename1,
          Max(Case When Ord = 2 Then Ename Else '' End) Ename2,
          Max(Case When Ord = 3 Then Ename Else '' End) Ename3
     From
         (
          Select Ename, Deptno,
                 Row_Number() Over (Partition By Deptno
                                        Order By Ename ) As Ord
            From Emp
         )
Group By Deptno;

    DEPTNO ENAME1     ENAME2     ENAME3
---------- ---------- ---------- ----------
        10 CLARK      KING       MILLER
        20 ADAMS      FORD       JONES
        30 ALLEN      BLAKE      JAMES