TSQL Pivot Table
TSQL Pivot Table? What’s that?
Here is a quick sample of how to implement a pivot table in TSQL for SQL Server. The example below creates a database called pivot, you probably already have your own database to work in. Then it creates a table called REVENUE and fills it in with department revenue for just over a 10 year period. Then you see a couple simple select statements followed by a SELECT statement that pivots the data.
TSQL Pivot Table examples
USE [Master]; set statistics io off; IF EXISTS(SELECT name FROM sys.databases WHERE name = 'pivot') BEGIN ALTER DATABASE [pivot] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE [pivot]; END CREATE DATABASE [pivot]; GO USE [pivot]; -- Table to be used for demo CREATE TABLE REVENUE ( [DepartmentID] int, [Revenue] int, [Year] int ); insert into REVENUE values (1,10030,1998),(2,20000,1998),(3,40000,1998), (1,20000,1999),(2,60000,1999),(3,50000,1999), (1,40000,2000),(2,40000,2000),(3,60000,2000), (1,30000,2001),(2,30000,2001),(3,70000,2001), (1,90000,2002),(2,20000,2002),(3,80000,2002), (1,10300,2003),(2,1000,2003), (3,90000,2003), (1,10000,2004),(2,10000,2004),(3,10000,2004), (1,20000,2005),(2,20000,2005),(3,20000,2005), (1,40000,2006),(2,30000,2006),(3,30000,2006), (1,70000,2007),(2,40000,2007),(3,40000,2007), (1,50000,2008),(2,50000,2008),(3,50000,2008), (1,20000,2009),(2,60000,2009),(3,60000,2009), (1,30000,2010),(2,70000,2010),(3,70000,2010), (1,80000,2011),(2,80000,2011),(3,80000,2011), (1,10000,2012),(2,90000,2012),(3,90000,2012); USE [pivot]; -- first lets look at the REVENUE table SELECT * FROM Revenue; SELECT DepartmentId, Year, Revenue FROM Revenue; -- Simple Pivot SELECT Year, [1], [2], [3] FROM (SELECT Year, DepartmentId, Revenue FROM Revenue) as t PIVOT ( sum(Revenue) FOR DepartmentId in ([1], [2], [3]) ) as pivotTable;
Just to recap, here is the actual pivot code, and the output that the TSQL Pivot Table produces.
-- Simple Pivot SELECT Year, [1], [2], [3] FROM (SELECT Year, DepartmentId, Revenue FROM Revenue) as t PIVOT ( sum(Revenue) FOR DepartmentId in ([1], [2], [3]) ) as pivotTable;
I hope you find this useful as an example of working with a TSQL Pivot Table in SQL Server using the select statement. For this and other examples, see the 70-461 training material and other TSQL related posts on this site.
See Also
- Database Health Monitor a free tool for SQL Server performance monitoring.
More from Stedman Solutions:
Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!
What kind of impact does Pivot have on the query plan?
If you are comparing the pivot to a simple select from the table, in the example shown it adds a sort, a stream aggregate, and a compute scalar beyond what the non-pivot example does. That may vary depending on the SQL Server version, or other factors on the query plan.
I hope that helps.
-Steve Stedman