DB2 Pivot table – 2

Few months back I had posted an example of how to create a pivot table using sql. That was how to create multiple rows into one column with values separated by comas. Well, this one shows how to transpose rows into columns.

Suppose I have a table : TYPE_COUNT

TYPE          COUNT
——           ———
ABC              10
BCD              20
CDE              15

and I want the results to look like this

ABC    BCD    CDE
—–    —–     —–
10         20       15

Here the value on the first column is now represented as column name.   This can be accomplished by using the DECODE function . The below sql will do the job:

SELECT
MAX(DECODE(type,’ABC’,count) AS ABC,
MAX(DECODE(type,’BCD’,count) AS BCD,
MAX(DECODE(type,’CDE’,count) AS CDE
FROM type_count;

The same can be done using a combination of MAX and CASE function, but then it take lot more typing .