This is one of the complex and often asked interview question on SQL. The question is “write a SQL query to convert rows into columns”. For beginners it is very difficult to write this query. The following answer will solve your problem.
You can write this Query in two steps and it executes in almost all major RDBMS databases like DB2,Oracle etc.
Let us see about what is PIVOT and how to create it:
What is PIVOT ?
The meaning of pivoting a table is to turn n-rows into n-columns. For example given a table with a composite primary key of year and quarter you may want to get a result that shows only one row for each year, but a separate column for each quarter.
Sample SQL from DB2 premium developers:
CREATE TABLE Sales(Year INTEGER NOT NULL, Quarter INTEGER NOT NULL, Results INTEGER); CREATE UNIQUE INDEX PK_IDX ON Sales(Year, Quarter) INCLUDE(Results); ALTER TABLE Sales ADD PRIMARY KEY (Year, Quarter); INSERT INTO Sales VALUES (2004, 1, 20), (2004, 2, 30), (2004, 3, 15), (2004, 4, 10), (2005, 1, 18), (2005, 2, 40), (2005, 3, 12), (2005, 4, 27);
The result you may need in PIVOT way is as follows
Year Q1 Q2 Q3 Q4 ---- -- -- -- -- 2004 20 30 15 10 2005 18 40 12 27
Now, how to make PIVOT table in the above way
There a many ways to make pivoting, but there is clearly one that is optimal which I will explain here.
Pivoting involves two events: First the payload (Results in this case) needs to be dispatch into the Q1 through Q4 columns.
This can be done with a CASE expression or DECODE
SELECT Year, DECODE(Quarter, 1, Results) AS Q1, DECODE(Quarter, 2, Results) AS Q2, DECODE(Quarter, 3, Results) AS Q3, DECODE(Quarter, 4, Results) AS Q4 FROM Sales; YEAR Q1 Q2 Q3 Q4 ----------- ----------- ----------- ----------- ----------- 2004 20 - - - 2004 - 30 - - 2004 - - 15 - 2004 - - - 10 2005 18 - - - 2005 - 40 - - 2005 - - 12 - 2005 - - - 27 8 record(s) selected.
Note how DECODE injects NULL as an implicit ELSE.
The second step is to collapse the rows to get one row per Year.
The technique of choice is a GROUP BY here. When grouping on Year we need to use some sort of column function for the columns Q1 through Q4.
MAX or MIN() work for most types and they are very cheap. Especially since all but one value will be NULL per group:
SELECT Year,
MAX(DECODE(Quarter, 1, Results)) AS Q1,
MAX(DECODE(Quarter, 2, Results)) AS Q2,
MAX(DECODE(Quarter, 3, Results)) AS Q3,
MAX(DECODE(Quarter, 4, Results)) AS Q4
FROM Sales
GROUP BY Year;
YEAR Q1 Q2 Q3 Q4
----------- ----------- ----------- ----------- -----------
2004 20 30 15 10
2005 18 40 12 27
2 record(s) selected.
Srinimf 