On the move:

I'm currently moving the following to their own blogs: Unix, HTML for Blogs, Perl & Java.
The links are on the left side.

Sunday, May 25, 2008

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

No comments:

Post a Comment