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