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