SQL Query You Need to Create Pivoting Table

This is a common and challenging question asked in SQL interviews. The task is to convert rows into columns using a SQL query. It can be difficult for beginners to do this, but the following answer will help you solve this problem.

You can approach this by writing the query in two steps, and it will work in most major databases like DB2 and Oracle.

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 DB2 SQL Query

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

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.