How to write SQL Query the best way for Pivoting Tables

Posted by

How to write SQL Query Converting Rows into Columns
[How to write SQL Query Converting Rows into Columns]
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.