PIVOT and UNPIVOT Operators in SQL Server(TSQL)

Ahmet KAYMAZ

5 years ago

The PIVOT , which is indispensable for Excel users, can be implemented with SQL Server 2005 via T-SQL. But I can’t say it’s as talented as Excel.

Consider the following ORDER table.

CREATE TABLE [dbo].[Order](
	[productid] [int] NULL,
	[year] [int] NULL,
	[number_of_sales] [int] NULL
) ON [PRIMARY]
GO
INSERT INTO  [dbo].[Order](productid,year,number_of_sales)
	VALUES (1,2005,10),
		   (1,2005,20),
		   (1,2006,4),
		   (2,2005,5),
		   (2,2007,14)
SELECT * FROM [dbo].[Order]

CREATE TABLE [dbo].[Order](

[productid] [int] NULL,

[year] [int] NULL,

[number_of_sales] [int] NULL

) ON [PRIMARY]

INSERT INTO [dbo].[Order](productid,year,number_of_sales)

VALUES (1,2005,10),

(1,2005,20),

(1,2006,4),

(2,2005,5),

(2,2007,14)

SELECT * FROM [dbo].[Order]

First, let’s find the total sales of the products according to years with classsic methods:

SELECT productid,
    SUM(case year when 2005 then number_of_sales else null end) as [2005],
    SUM(case year when 2006 then number_of_sales else null end) as [2006],
    SUM(case year when 2007 then number_of_sales else null end) as [2007]
FROM [dbo].[Order]
GROUP BY productid

SELECT productid,

SUM(case year when 2005 then number_of_sales else null end) as [2005],

SUM(case year when 2006 then number_of_sales else null end) as [2006],

SUM(case year when 2007 then number_of_sales else null end) as [2007]

FROM [dbo].[Order]

GROUP BY productid

Let’s do the same with PIVOT:

We are creating a table for only the relevant columns. It can also be a Temp table.

--We are creating a table for only the relevant columns. It can also be a Temp table.

SELECT productid , year , number_of_sales INTO Order1 FROM [dbo].[Order]
 
SELECT productid, [2005], [2006], [2007] FROM Order1
PIVOT (
    SUM(number_of_sales)
    FOR year IN ([2005],[2006],[2007])
) b

--We are creating a table for only the relevant columns. It can also be a Temp table.

SELECT productid , year , number_of_sales INTO Order1 FROM [dbo].[Order]

SELECT productid, [2005], [2006], [2007] FROM Order1

PIVOT (

SUM(number_of_sales)

FOR year IN ([2005],[2006],[2007])

) b

Or, without creating a temporary table, we can read the columns directly from the Order Table with subquery.

SELECT productid, [2005], [2006], [2007] FROM
    (
        SELECT productid, [year], number_of_sales FROM [dbo].[Order]
    ) a
PIVOT (
    SUM(number_of_sales)
    FOR [year] IN ([2005],[2006],[2007])
) b

SELECT productid, [2005], [2006], [2007] FROM

(

SELECT productid, [year], number_of_sales FROM [dbo].[Order]

) a

PIVOT (

SUM(number_of_sales)

FOR [year] IN ([2005],[2006],[2007])

) b

Likewise, we can change the position of columns and rows using the UNPIVOT.

SELECT productid, [2005], [2006], [2007] INTO PivotTable FROM
    (
        SELECT productid, [year], number_of_sales FROM [dbo].[Order]
    ) a
PIVOT (
    SUM(number_of_sales)
    FOR [year] IN ([2005],[2006],[2007])
) b

--SELECT * FROM PivotTable
 
SELECT productid, [year], number_of_sales FROM PivotTable
UNPIVOT (number_of_sales FOR [year] IN ([2005],[2006],[2007]) ) as Result

SELECT productid, [2005], [2006], [2007] INTO PivotTable FROM

(

SELECT productid, [year], number_of_sales FROM [dbo].[Order]

) a

PIVOT (

SUM(number_of_sales)

FOR [year] IN ([2005],[2006],[2007])

) b

--SELECT * FROM PivotTable

SELECT productid, [year], number_of_sales FROM PivotTable

UNPIVOT (number_of_sales FOR [year] IN ([2005],[2006],[2007]) ) as Result