Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 | 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] |
First, let’s find the total sales of the products according to years with classsic methods:
1 2 3 4 5 6 | 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.
1 2 3 4 5 6 7 8 9 | --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.
1 2 3 4 5 6 7 8 | 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 | 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 |
