---
title: "SQL Query You Need to Create Pivoting Table"
description: "The best and mostly asked SQL query is converting rows into columns. This is a tricky question and it needs little intelligence to write effectively. This SQL query can be written in two steps with SQL functions either CASE or DECODE."
url: https://srinimf.com/2016/10/01/how-to-write-sql-query-the-best-way-for-pivoting-tables/
date: 2016-10-01
modified: 2026-10-10
author: "Srini"
image: https://i0.wp.com/srinimf.com/wp-content/uploads/2016/10/pexels-photo-3182746.jpeg?fit=1880%2C1253&ssl=1
categories: ["SQL", "SQL Functions"]
tags: ["SQL tips and tricks"]
type: post
lang: en
---

# 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](https://srinimf.com/2016/01/23/db2-complex-sql-query-on-self-join-example/) 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 ](https://srinimf.com/2012/07/25/complex-sql-queries/)

```
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.
```

### Share this:

-
- [Tweet](https://twitter.com/share)
- [![](https://i0.wp.com/assets.pinterest.com/images/pidgets/pinit_fg_en_rect_gray_20.png)](https://www.pinterest.com/pin/create/link/?url=https%3A%2F%2Fsrinimf.com%2F2016%2F10%2F01%2Fhow-to-write-sql-query-the-best-way-for-pivoting-tables%2F&media=https%3A%2F%2Fi0.wp.com%2Fsrinimf.com%2Fwp-content%2Fuploads%2F2016%2F10%2Fpexels-photo-3182746.jpeg%3Ffit%3D1200%252C800%26ssl%3D1&description=SQL%20Query%20You%20Need%20to%20Create%20Pivoting%20Table)
-
- [Share on Tumblr](https://www.tumblr.com/share)
- [Share on WhatsApp (Opens in new window) WhatsApp](https://srinimf.com/2016/10/01/how-to-write-sql-query-the-best-way-for-pivoting-tables/?share=jetpack-whatsapp)
- [More](#)
-

- [Print (Opens in new window) Print](https://srinimf.com/2016/10/01/how-to-write-sql-query-the-best-way-for-pivoting-tables/?share=print)
- [Share on Reddit (Opens in new window) Reddit](https://srinimf.com/2016/10/01/how-to-write-sql-query-the-best-way-for-pivoting-tables/?share=reddit)
- [Share on Telegram (Opens in new window) Telegram](https://srinimf.com/2016/10/01/how-to-write-sql-query-the-best-way-for-pivoting-tables/?share=telegram)
- [Email a link to a friend (Opens in new window) Email](mailto:?subject=%5BShared%20Post%5D%20SQL%20Query%20You%20Need%20to%20Create%20Pivoting%20Table&body=https%3A%2F%2Fsrinimf.com%2F2016%2F10%2F01%2Fhow-to-write-sql-query-the-best-way-for-pivoting-tables%2F&share=email)
-

### Like this:

Like Loading…