Using PIVOT and UNPIVOT

Converting Rows to Columns – PIVOT

SQL Server has a PIVOT relational operator to turn the unique values of a specified column from multiple rows into multiple column values in the output (cross-tab), effectively rotating a table. It also allows performing aggregations, wherever required, for column values that are expected in the final output. The basic syntax for a PIVOT relational operator looks like this:

SELECT <<ColumnNames>> 
FROM <<TableName>> 
PIVOT
 (
   AggregateFunction(<<ColumnToBeAggregated>>)
   FOR PivotColumn IN (<<PivotColumnValues>>)
 ) AS <<Alias>>

Example:

CREATE TABLE [dbo].[PivotExample](
[Country]   [nvarchar](50)   NULL,
[Year]   [smallint] NOT NULL,
[SalesAmount]   [money] NULL
)

GO

INSERT  [dbo].[PivotExample]  ([Country],  [Year], [SalesAmount])  VALUES (N'Australia', 2015, 1309.20)
INSERT  [dbo].[PivotExample]  ([Country],  [Year], [SalesAmount])  VALUES (N'Germany', 2016, 5212.85)
INSERT  [dbo].[PivotExample]  ([Country],  [Year], [SalesAmount])  VALUES (N'United States',   2017, 2838.35)
INSERT  [dbo].[PivotExample]  ([Country],  [Year], [SalesAmount])  VALUES (N'France', 2018, 9221.04)
INSERT  [dbo].[PivotExample]  ([Country],  [Year], [SalesAmount])  VALUES (N'Australia', 2017, 30337.21)
INSERT  [dbo].[PivotExample]  ([Country],  [Year], [SalesAmount])  VALUES (N'France', 2015, 1805.69)
INSERT  [dbo].[PivotExample]  ([Country],  [Year], [SalesAmount])  VALUES (N'United Kingdom',   2016, 5915.85)
INSERT  [dbo].[PivotExample]  ([Country],  [Year], [SalesAmount])  VALUES (N'Canada', 2016, 6216.38)
INSERT  [dbo].[PivotExample]  ([Country],  [Year], [SalesAmount])  VALUES (N'United Kingdom',   2015, 2915.51)
INSERT  [dbo].[PivotExample]  ([Country],  [Year], [SalesAmount])  VALUES (N'United States',   2015, 1100.44)
INSERT  [dbo].[PivotExample]  ([Country],  [Year], [SalesAmount])  VALUES (N'Canada', 2017, 5357.46)
INSERT  [dbo].[PivotExample]  ([Country],  [Year], [SalesAmount])  VALUES (N'France', 2017, 1026.96)
INSERT  [dbo].[PivotExample]  ([Country],  [Year], [SalesAmount])  VALUES (N'Germany', 2017, 1055.73)
INSERT  [dbo].[PivotExample]  ([Country],  [Year], [SalesAmount])  VALUES (N'Australia', 2016, 2154.88)
INSERT  [dbo].[PivotExample]  ([Country],  [Year], [SalesAmount])  VALUES (N'United Kingdom',   2018, 1210.27)
INSERT  [dbo].[PivotExample]  ([Country],  [Year], [SalesAmount])  VALUES (N'United States',   2018, 3324.16)
INSERT  [dbo].[PivotExample]  ([Country],  [Year], [SalesAmount])  VALUES (N'Germany', 2018, 1076.77)
INSERT  [dbo].[PivotExample]  ([Country],  [Year], [SalesAmount])  VALUES (N'United Kingdom',   2017, 1298.56)
INSERT  [dbo].[PivotExample]  ([Country],  [Year], [SalesAmount])  VALUES (N'Australia', 2018, 2563.29)
INSERT  [dbo].[PivotExample]  ([Country],  [Year], [SalesAmount])  VALUES (N'Canada', 2015, 1468.80)
INSERT  [dbo].[PivotExample]  ([Country],  [Year], [SalesAmount])  VALUES (N'Germany', 2015, 2377.99)
INSERT  [dbo].[PivotExample]  ([Country],  [Year], [SalesAmount])  VALUES (N'Canada', 2018, 6736.21)
INSERT  [dbo].[PivotExample]  ([Country],  [Year], [SalesAmount])  VALUES (N'United States',   2016, 2126.54)
INSERT  [dbo].[PivotExample]  ([Country],  [Year], [SalesAmount])  VALUES (N'France', 2016, 5149.01)
INSERT  [dbo].[PivotExample]  ([Country],  [Year], [SalesAmount])  VALUES (N'India', 2016, 6149.01)
INSERT  [dbo].[PivotExample]  ([Country],  [Year], [SalesAmount])  VALUES (N'India', 2019, 7549.01)

GO

SELECT * FROM [dbo].[PivotExample] ORDER   BY Country

GO

Now by using the PIVOT operator, we will convert row values into column values.

SELECT   [Country], [2015],   [2016], [2017],   [2018] , [2019]
FROM   [dbo].[PivotExample] 
PIVOT
(
       SUM(SalesAmount)
       FOR [Year] IN ([2015], [2016], [2017], [2018], [2019])
) AS P

Filter Pivoted Table

SELECT * FROM (
SELECT   [Country], [2015],   [2016], [2017],   [2018] , [2019]
FROM   [dbo].[PivotExample] 
PIVOT
(
       SUM(SalesAmount)
       FOR [Year] IN ([2015], [2016], [2017], [2018], [2019])
) AS P) as Q where Country='India'

GO

Converting Columns to Rows – UNPIVOT

UNPIVOT is another relational operator in SQL Server that performs almost the reverse operation of PIVOT, by rotating column values into rows values.

Example:

SELECT   [Country], [2015],   [2016], [2017],   [2018] ,[2019]
INTO   [dbo].[UnpivotExample]
FROM   [dbo].[PivotExample] 
PIVOT
(
       SUM(SalesAmount)
       FOR [Year] IN ([2015], [2016], [2017], [2018],[2019])
) AS P

GO

SELECT * FROM [dbo].[UnpivotExample] ORDER   BY Country

GO

Now by using the UNPIVOT operator, we will convert Column values into Rows values.

SELECT   Country, Year, SalesAmount 
FROM [dbo].[UnpivotExample]
UNPIVOT
(
       SalesAmount
       FOR [Year] IN ([2015], [2016], [2017], [2018] ,[2019])
) AS P