Pivoting a Result Set into One Row

Problem

You wish to take values from groups of rows and turn those values into columns in a single row per group. For example, you have a result set displaying the number of employees in each department:

	DEPTNO        CNT
	------ ----------
	    10          3
	    20          5
	    30          6

You would like to reformat the output such the result set looks as follows:

	DEPTNO_10   DEPTNO_20   DEPTNO_30
	---------  ----------  ----------
	        3           5           6

Solution

Transpose the result set using a CASE expression and the aggregate function SUM:

	1 select sum(case when deptno=10 then 1 else 0 end) as deptno_10,
	2        sum(case when deptno=20 then 1 else 0 end) as deptno_20,
	3        sum(case when deptno=30 then 1 else 0 end) as deptno_30
	4   from emp

Discussion

This example is an excellent introduction to pivoting. The concept is simple: for each row returned by the unpivoted query, use a CASE expression to separate the rows into columns. Then, because this particular problem is to count the number of employees per department, use the aggregate function SUM to count the occurrence of each DEPTNO. If you're having trouble understanding how this works exactly, execute the query with the aggregate function SUM and include DEPTNO for readability:

	
	select deptno,
	       case when deptno=10 then 1 else 0 end as deptno_10,
	       case when deptno=20 then 1 else 0 end as deptno_20,
	       case when deptno=30 then 1 else 0 end as deptno_30
	  from emp
	 order by 1

	 DEPTNO   DEPTNO_10   DEPTNO_20   DEPTNO_30
	 ------  ----------  ----------  ----------
	     10           1           0           0
	     10           1           0           0
	     10           1           0           0
	     20           0           1           0
	     20           0           1           0
	     20           0           1           0
	     20           0           1           0
	     30           0           0           1
	     30           0           0           1
	     30           0           0           1
	     30           0           0           1
	     30           0           0           1
	     30           0           0           1

You can think of each CASE expression as a flag to determine which DEPTNO a row belongs to. At this point, the "rows to columns" transformation is already done; the next step is to simply sum the values returned by DEPTNO_10, DEPTNO_20, and DEPTNO_30, and then to group by DEPTNO. Following are the results:

	
	select deptno,
	       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
	 group by deptno

	DEPTNO   DEPTNO_10   DEPTNO_20   DEPTNO_30
	------  ----------  ----------  ----------
	    10           3           0           0
	    20           0           5           0
	    30           0           0           6

If you eyeball this result set, you see that logically the output makes sense; for example, DEPTNO 10 has 3 employees in DEPTNO_10 and zero in the other departments. Since the goal is to return one row, the last step is to lose the DEPTNO and GROUP BY, and simply sum the CASE expressions:

	
	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

Following is another approach that you may sometimes see applied to this same sort of problem:

	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=10 then empcount else null end) as deptno_30
	  from (
	select deptno, count(*) as empcount
	  from emp
	 group by deptno
	       ) x

This approach uses an inline view to generate the employee counts per department. CASE expressions in the main query translate rows to columns, getting you to the following results:

	DEPTNO_10   DEPTNO_20   DEPTNO_30
	---------  ----------  ----------
	        3        NULL        NULL
	     NULL           5        NULL
	     NULL        NULL           6

Then the MAX functions collapses the columns into one row:

	DEPTNO_10   DEPTNO_20   DEPTNO_30
	---------  ----------  ----------
	        3           5           6