pivoting a result set | Jinny's Blog

= Pivoting a Result Set into Multiple Rows

mysql> select job, ename from EMP order by job;
+-----------+--------+
| job       | ename  |
+-----------+--------+
| ANALYST   | SCOTT  |
| ANALYST   | FORD   |
| CLERK     | JAMES  |
| CLERK     | ADAMS  |
| CLERK     | SMITH  |
| CLERK     | MILLER |
| MANAGER   | BLAKE  |
| MANAGER   | CLARK  |
| MANAGER   | JONES  |
| PRESIDENT | KING   |
| SALESMAN  | MARTIN |
| SALESMAN  | TURNER |
| SALESMAN  | WARD   |
| SALESMAN  | ALLEN  |
+-----------+--------+
14 rows in set (0.00 sec)

== Solution: MySQL

You would like to format the result set such that each job gets its own column:

 
mysql> select max(case when job ='clerk' then ename else null end) as clerks,
    ->   max(case when job ='analyst' then ename else null end) as analysts,
    ->   max(case when job ='manager' then ename else null end) as managers,
    ->   max(case when job ='president' then ename else null end) as presidents,
    ->   max(case when job ='salesman' then ename else null end) as salesmen
    -> from ( select e.job, e.ename,
    ->                (select count(*) from EMP d
    ->                   where e.job = d.job and e.empno < d.empno) as rnk
    ->         from EMP e
    ->       ) x
    -> group by rnk;
+--------+----------+----------+------------+----------+
| clerks | analysts | managers | presidents | salesmen |
+--------+----------+----------+------------+----------+
| MILLER | FORD     | CLARK    | KING       | TURNER   |
| JAMES  | SCOTT    | BLAKE    | NULL       | MARTIN   |
| ADAMS  | NULL     | JONES    | NULL       | WARD     |
| SMITH  | NULL     | NULL     | NULL       | ALLEN    |
+--------+----------+----------+------------+----------+
4 rows in set (0.00 sec)

mysql> select deptno, max(case when job ='clerk' then ename else null end) as clerks,
    ->   max(case when job ='analyst' then ename else null end) as analysts,
    ->   max(case when job ='manager' then ename else null end) as managers,
    ->   max(case when job ='president' then ename else null end) as presidents,
    ->   max(case when job ='salesman' then ename else null end) as salesmen
    -> from ( select e.deptno, e.job, e.ename,
    ->                (select count(*) from EMP d
    ->                   where e.job = d.job and e.empno < d.empno) as rnk
    ->         from EMP e
    ->       ) x
    -> group by deptno, rnk;
+--------+--------+----------+----------+------------+----------+
| deptno | clerks | analysts | managers | presidents | salesmen |
+--------+--------+----------+----------+------------+----------+
|     10 | MILLER | NULL     | CLARK    | KING       | NULL     |
|     20 | NULL   | FORD     | NULL     | NULL       | NULL     |
|     20 | NULL   | SCOTT    | NULL     | NULL       | NULL     |
|     20 | ADAMS  | NULL     | JONES    | NULL       | NULL     |
|     20 | SMITH  | NULL     | NULL     | NULL       | NULL     |
|     30 | NULL   | NULL     | NULL     | NULL       | TURNER   |
|     30 | JAMES  | NULL     | BLAKE    | NULL       | MARTIN   |
|     30 | NULL   | NULL     | NULL     | NULL       | WARD     |
|     30 | NULL   | NULL     | NULL     | NULL       | ALLEN    |
+--------+--------+----------+----------+------------+----------+
9 rows in set (0.00 sec)

== Solution: Oracle, SQL Server

select max(case when job ='clerk' then ename else null end) as clerks,
        max(case when job ='analyst' then ename else null end) as analysts,
        max(case when job ='manager' then ename else null end) as managers,
        max(case when job ='president' then ename else null end) as presidents,
        max(case when job ='salesman' then ename else null end) as salesmen
from ( select job, ename, 
                row_number() over(partition by job order by ename) rn
        from EMP 
      ) x
group by rn;

mysql> select deptno, count(*) from EMP group by deptno; +--------+----------+ | deptno | count(*) | +--------+----------+ | 10 | 3 | | 20 | 5 | | 30 | 6 | +--------+----------+ 3 rows in set (0.02 sec)

mysql> select sum(case when deptno = 10 then 1 else 0 end) as deptno_10, -> sum(case when deptno = 20 then 1 else 0 end) as deptno_20, -> sum(case when deptno = 30 then 1 else 0 end) as deptno_30 -> from EMP; +-----------+-----------+-----------+ | deptno_10 | deptno_20 | deptno_30 | +-----------+-----------+-----------+ | 3 | 5 | 6 | +-----------+-----------+-----------+ 1 row in set (0.11 sec)

mysql> select max(case when deptno = 10 then empcount else null end) as deptno_10, -> max(case when deptno = 20 then empcount else null end) as deptno_20, -> max(case when deptno = 30 then empcount else null end) as deptno_30 -> from ( select deptno, count(*) as empcount -> from EMP group by deptno -> ) x; +-----------+-----------+-----------+ | deptno_10 | deptno_20 | deptno_30 | +-----------+-----------+-----------+ | 3 | 5 | 6 | +-----------+-----------+-----------+ 1 row in set (0.00 sec)

Jinny's Blog

Developer Notes

Tag Archives: pivoting a result set

[MySQL] Pivoting a Result Set into Multiple Rows

[MySQL] Pivoting a Result Set into One Row