TSQL Pivot Table on Microsoft SQL Server

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.

TSQL Pivot Table

TSQL Pivot Table examples

See Also

Leave a Reply Cancel reply