TSQL Pivot Table
TSQL Pivot Table? Whats 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!
Leave a Reply