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:
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
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
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
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
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
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
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
|
