Recipe12.4.Reverse Pivoting a Result Set into One Column

Recipe 12.4. Reverse Pivoting a Result Set into One Column

Problem

You want to return all columns from a query as just one column. For example, you want to return the ENAME, JOB, and SAL of all employees in DEPTNO 10, and you want to return all three values in one column. You want to return three rows for each employee and one row of white space between employees. You want to return the following result set:

 EMPS ---------- CLARK MANAGER 2450 KING PRESIDENT 5000 MILLER CLERK 1300 

Solution

The key is to use a Cartesian product to return four rows for each employee. This lets you return one column value per row and have an extra row for spacing between employees.

DB2, Oracle, and SQL Server

Use the window function ROW_NUMBER OVER to rank each row based on EMPNO (14). Then use a CASE expression to transform three columns into one:

  1 select case rn  2             when 1 then ename  3             when 2 then job  4             when 3 then cast(sal as char(4))  5        end emps  6   from (  7 select e.ename,e.job,e.sal,  8        row_number()over(partition by e.empno  9                             order by e.empno) rn 10   from emp e, 11        (select * 12           from emp where job='CLERK') four_rows 13  where e.deptno=10 14        ) x 

PostgreSQL and MySQL

This recipe is meant to highlight the use of window functions to provide a ranking for your rows, which then comes into play later when pivoting. At the time of this writing, neither PostgreSQL nor MySQL support window functions.

Discussion

DB2, Oracle, and SQL Server

The first step is to use the window function ROW_NUMBER OVER to create a ranking for each employee in DEPTNO 10:

  select e.ename,e.job,e.sal,        row_number()over(partition by e.empno                             order by e.empno) rn   from emp e  where e.deptno=10 ENAME       JOB             SAL         RN ---------- --------- ---------- ---------- CLARK      MANAGER         2450          1 KING       PRESIDENT       5000          1 MILLER     CLERK           1300          1 

At this point the ranking doesn't mean much. You are partitioning by EMPNO, so the rank is 1 for all three rows in DEPTNO 10. Once you add the Cartesian product, the rank will begin to take shape, as can be seen in the following results:

  select e.ename,e.job,e.sal,        row_number()over(partition by e.empno                             order by e.empno) rn   from emp e,        (select *           from emp where job='CLERK') four_rows  where e.deptno=10 ENAME      JOB              SAL         RN ---------- --------- ---------- ---------- CLARK      MANAGER         2450          1 CLARK      MANAGER         2450          2 CLARK      MANAGER         2450          3 CLARK      MANAGER         2450          4 KING       PRESIDENT       5000          1 KING       PRESIDENT       5000          2 KING       PRESIDENT       5000          3 KING       PRESIDENT       5000          4 MILLER     CLERK           1300          1 MILLER     CLERK           1300          2 MILLER     CLERK           1300          3 MILLER     CLERK           1300          4 

You should stop at this point and understand two key points:

• RN is no longer 1 for each employee; it is now a repeating sequence of values from 1 to 4, the reason being, window functions are applied after the FROM and WHERE clauses are evaluated. So, partitioning by EMPNO causes the RN to reset to 1 when a new employee is encountered.

• The inline view FOUR_ROWS is simply that a SQL statement exists simply to return four rows. That is all it does. You want to return a row for every column (ENAME, JOB, SAL) plus an additional row for whitespace.

At this point, the hard work is done and all that is left is to use a CASE expression to put ENAME, JOB, and SAL into one column for each employee (you need to cast SAL to a string to make CASE happy):

  select case rn             when 1 then ename             when 2 then joB               when 3 then cast(sal as char(4))        end emps   from ( Select e.ename,e.job,e.sal,        row_number()over(partition by e.empno                             order by e.empno) rn   from emp e,        (select *           from emp where job='CLERK') four_rows  where e.deptno=10        ) x EMPS ---------- CLARK MANAGER 2450 KING PRESIDENT 5000 MILLER CLERK 1300