In my Are you ready for SQL Server 2012 or are you still partying like it is 1999? post, I wrote about how you should start using SQL Server 2005 and SQL Server 2008 functionality now in order to prepare for SQL Server 2012. I still see tons of code that is written in the pre 2005 style and people still keep using those functions, procs and statements even though SQL Server 2005 and 2008 have much better functionality.

In today’s post I will cover the PIVOT operator

The simplest PIVOT example

In order to show you the simplest example of doing a pivot, create this table

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE TABLE #test (id int, Value char(1))
INSERT #test VALUES(1,'A')
INSERT #test VALUES(2,'A')
INSERT #test VALUES(1,'B')
INSERT #test VALUES(2,'B')
INSERT #test VALUES(1,'C')
INSERT #test VALUES(2,'C')
INSERT #test VALUES(1,'D')
INSERT #test VALUES(2,'D')
INSERT #test VALUES(1,'E')
INSERT #test VALUES(2,'E')
INSERT #test VALUES(1,'F')
INSERT #test VALUES(2,'F')
INSERT #test VALUES(3,'F')
CREATE TABLE #test (id int, Value char(1))
INSERT #test VALUES(1,'A')
INSERT #test VALUES(2,'A')
INSERT #test VALUES(1,'B')
INSERT #test VALUES(2,'B')
INSERT #test VALUES(1,'C')
INSERT #test VALUES(2,'C')
INSERT #test VALUES(1,'D')
INSERT #test VALUES(2,'D')
INSERT #test VALUES(1,'E')
INSERT #test VALUES(2,'E')
INSERT #test VALUES(1,'F')
INSERT #test VALUES(2,'F')
INSERT #test VALUES(3,'F')

What we want is the following output

A	B	C	D	E	F
3	3	3	3	3	6

So for each distinct value in the Value column we want to display it in its own column and sum up the values of the ID column

The query below show SQL Server 2000 syntax to do a pivot

T-SQL
1
2
3
4
5
6
7
SELECT  SUM(CASE WHEN Value ='A' THEN ID ELSE 0 END) AS A,
        SUM(CASE WHEN Value ='B' THEN ID ELSE 0 END) AS B,
        SUM(CASE WHEN Value ='C' THEN ID ELSE 0 END) AS C,
        SUM(CASE WHEN Value ='D' THEN ID ELSE 0 END) AS D,
        SUM(CASE WHEN Value ='E' THEN ID ELSE 0 END) AS E,
        SUM(CASE WHEN Value ='F' THEN ID ELSE 0 END) AS F
FROM #test
SELECT	SUM(CASE WHEN Value ='A' THEN ID ELSE 0 END) AS A,
		SUM(CASE WHEN Value ='B' THEN ID ELSE 0 END) AS B,
		SUM(CASE WHEN Value ='C' THEN ID ELSE 0 END) AS C,
		SUM(CASE WHEN Value ='D' THEN ID ELSE 0 END) AS D,
		SUM(CASE WHEN Value ='E' THEN ID ELSE 0 END) AS E,
		SUM(CASE WHEN Value ='F' THEN ID ELSE 0 END) AS F
FROM #test

And here is SQL Server 2005 and up syntax

T-SQL
1
2
3
4
5
6
7
8
SELECT * FROM
(   SELECT *
    FROM #test
) AS pivTemp
PIVOT
(   SUM(ID)
    FOR Value IN ( A,B,C,D,E,F )
) AS pivTable
SELECT * FROM
(	SELECT *
	FROM #test
) AS pivTemp
PIVOT
(   SUM(ID)
    FOR Value IN ( A,B,C,D,E,F )
) AS pivTable

As you can see the 2005 syntax is much cleaner. At first sight the PIVOT syntax looks really complicated, it is really not once you get used to it.

This part

SUM(ID)
FOR Value IN(C,D)

Is the same as

SUM(CASE WHEN Value =’C’ THEN ID ELSE 0 END) AS C,
SUM(CASE WHEN Value =’D’ THEN ID ELSE 0 END) AS D,

So the value that is in the Value column goes in the IN(C,D), the column you want to sum goes in the SUM(ID) part

Some more pivot examples

Let’s create a couple of tables and populate them with some data

The first table will have some IDs and some values tied to those IDs

T-SQL
1
2
3
4
5
6
CREATE TABLE SomeTable(ID INT,IdValue VARCHAR(100))
INSERT SomeTable VALUES(1,'SomeDate')
INSERT SomeTable VALUES(2,'SomeName')
INSERT SomeTable VALUES(3,'SomeValue')
INSERT SomeTable VALUES(4,'SomeID')
INSERT SomeTable VALUES(5,'SomeNumber')
CREATE TABLE SomeTable(ID INT,IdValue VARCHAR(100))
INSERT SomeTable VALUES(1,'SomeDate')
INSERT SomeTable VALUES(2,'SomeName')
INSERT SomeTable VALUES(3,'SomeValue')
INSERT SomeTable VALUES(4,'SomeID')
INSERT SomeTable VALUES(5,'SomeNumber')

This table will have a bunch of employees

T-SQL
1
2
3
4
5
6
CREATE TABLE SomeEmployees (SomeID INT, SomeName VARCHAR(100))
INSERT SomeEmployees VALUES (1,'Denis')
INSERT SomeEmployees VALUES (2,'Chris')
INSERT SomeEmployees VALUES (3,'Ted')
INSERT SomeEmployees VALUES (4,'Eli')
INSERT SomeEmployees VALUES (5,'George')
CREATE TABLE SomeEmployees (SomeID INT, SomeName VARCHAR(100))
INSERT SomeEmployees VALUES (1,'Denis')
INSERT SomeEmployees VALUES (2,'Chris')
INSERT SomeEmployees VALUES (3,'Ted')
INSERT SomeEmployees VALUES (4,'Eli')
INSERT SomeEmployees VALUES (5,'George')

The final table will have values for each employee from the SomeTable. So each employee will have a SomeDate value etc etc

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
CREATE TABLE SomeValue (SomeID INT,ID INT,Value VARCHAR(100))
--Denis
INSERT SomeValue VALUES (1,1,'2011-01-01')
INSERT SomeValue VALUES (1,2,'Bla')
INSERT SomeValue VALUES (1,3,'5')
INSERT SomeValue VALUES (1,4,'100')
INSERT SomeValue VALUES (1,5,'300')
 
 
--Chris
INSERT SomeValue VALUES (2,1,'2012-01-01')
INSERT SomeValue VALUES (2,2,'Bla2')
INSERT SomeValue VALUES (2,3,'8')
INSERT SomeValue VALUES (2,4,'200')
INSERT SomeValue VALUES (2,5,'400')
 
--Ted
INSERT SomeValue VALUES (3,1,'2013-01-01')
INSERT SomeValue VALUES (3,2,'Bla3')
INSERT SomeValue VALUES (3,3,'9')
INSERT SomeValue VALUES (3,4,'300')
INSERT SomeValue VALUES (3,5,'500')
CREATE TABLE SomeValue (SomeID INT,ID INT,Value VARCHAR(100))
--Denis
INSERT SomeValue VALUES (1,1,'2011-01-01')
INSERT SomeValue VALUES (1,2,'Bla')
INSERT SomeValue VALUES (1,3,'5')
INSERT SomeValue VALUES (1,4,'100')
INSERT SomeValue VALUES (1,5,'300')


--Chris
INSERT SomeValue VALUES (2,1,'2012-01-01')
INSERT SomeValue VALUES (2,2,'Bla2')
INSERT SomeValue VALUES (2,3,'8')
INSERT SomeValue VALUES (2,4,'200')
INSERT SomeValue VALUES (2,5,'400')

--Ted
INSERT SomeValue VALUES (3,1,'2013-01-01')
INSERT SomeValue VALUES (3,2,'Bla3')
INSERT SomeValue VALUES (3,3,'9')
INSERT SomeValue VALUES (3,4,'300')
INSERT SomeValue VALUES (3,5,'500')

If we now query these 3 table with the following query

T-SQL
1
2
3
SELECT * FROM SomeTable st 
JOIN SomeValue sv ON st.ID = sv.ID
JOIN SomeEmployees se ON se.SomeID = sv.SomeID
SELECT * FROM SomeTable st 
JOIN SomeValue sv ON st.ID = sv.ID
JOIN SomeEmployees se ON se.SomeID = sv.SomeID

We get this resultset back

ID IdValue SomeID ID Value SomeID SomeName
1 SomeDate 1 1 2011-01-01 1 Denis
2 SomeName 1 2 Bla 1 Denis
3 SomeValue 1 3 5 1 Denis
4 SomeID 1 4 100 1 Denis
5 SomeNumber 1 5 300 1 Denis
1 SomeDate 2 1 2012-01-01 2 Chris
2 SomeName 2 2 Bla2 2 Chris
3 SomeValue 2 3 8 2 Chris
4 SomeID 2 4 200 2 Chris
5 SomeNumber 2 5 400 2 Chris
1 SomeDate 3 1 2013-01-01 3 Ted
2 SomeName 3 2 Bla3 3 Ted
3 SomeValue 3 3 9 3 Ted
4 SomeID 3 4 300 3 Ted
5 SomeNumber 3 5 500 3 Ted

What if we want this instead?

TheName SomeValue Somedate SomeID SomeName SomeNumber
Chris 8 2012-01-01 200 Bla2 400
Denis 5 2011-01-01 100 Bla 300
Ted 9 2013-01-01 300 Bla3 500

Again, we can use PIVOT to accomplish that, here is what the query looks like

T-SQL
1
2
3
4
5
6
7
8
9
10
SELECT * FROM
(   SELECT Value ,IdValue,SomeName as TheName
    FROM SomeTable st 
    JOIN SomeValue sv ON st.ID = sv.ID
    JOIN SomeEmployees se ON se.SomeID = sv.SomeID
) AS pivTemp
PIVOT
(   MAX(Value)
    FOR IdValue IN ( SomeValue,Somedate, SomeID,SomeNAme,SomeNumber )
) AS pivTable
SELECT * FROM
(	SELECT Value ,IdValue,SomeName as TheName
	FROM SomeTable st 
	JOIN SomeValue sv ON st.ID = sv.ID
	JOIN SomeEmployees se ON se.SomeID = sv.SomeID
) AS pivTemp
PIVOT
(   MAX(Value)
    FOR IdValue IN ( SomeValue,Somedate, SomeID,SomeNAme,SomeNumber )
) AS pivTable

That PIVOT query is very nice but what happens when I add a new type in the SomeTable table and name it SomeJunk. I would have to modify my PIVOT query and add
SomeJunk to the line below

FOR IdValue IN ( SomeValue,Somedate, SomeID,SomeNAme,SomeNumber )

You can solve this by using dynamic SQL, you dump the columns in a variable and then you dynamically construct the statement so that what get’s executed will have all the column values.

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
CREATE PROC prCrossTab
AS
 
DECLARE @ColumnNames NVARCHAR(MAX) = ''
SELECT    @ColumnNames += QUOTENAME(RTRIM(IdValue)) +', '  
FROM SomeTable 
      
 
SELECT @ColumnNames = LEFT(@ColumnNames,(LEN(@ColumnNames) -1))
 
DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL= ('
SELECT * FROM
(   SELECT Value ,IdValue,SomeName as TheName
    FROM SomeTable st 
    JOIN SomeValue sv ON st.ID = sv.ID
    JOIN SomeEmployees se ON se.SomeID = sv.SomeID
) AS pivTemp
PIVOT
(   MAX(Value)
    FOR IdValue IN ('+ @ColumnNames  + ' )
) AS pivTable')
 
 
EXEC sp_executesql @SQL
GO
CREATE PROC prCrossTab
AS

DECLARE @ColumnNames NVARCHAR(MAX) = ''
SELECT    @ColumnNames += QUOTENAME(RTRIM(IdValue)) +', '  
FROM SomeTable 
      

SELECT @ColumnNames = LEFT(@ColumnNames,(LEN(@ColumnNames) -1))

DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL= ('
SELECT * FROM
(	SELECT Value ,IdValue,SomeName as TheName
	FROM SomeTable st 
	JOIN SomeValue sv ON st.ID = sv.ID
	JOIN SomeEmployees se ON se.SomeID = sv.SomeID
) AS pivTemp
PIVOT
(   MAX(Value)
    FOR IdValue IN ('+ @ColumnNames  + ' )
) AS pivTable')


EXEC sp_executesql @SQL
GO

Now run the stored procedure

T-SQL
1
EXEC prCrossTab
EXEC prCrossTab

output

TheName SomeValue Somedate SomeID SomeName SomeNumber
Chris 2012-01-01 Bla2 8 200 400
Denis 2011-01-01 Bla 5 100 300
Ted 2013-01-01 Bla3 9 300 500

As you can see it is the same as the PIVOT query which wasn’t dynamic

Now add another type and add also rows for employees with values for this type

T-SQL
1
2
3
4
5
INSERT SomeTable VALUES(6,'SomeJunk')
 
INSERT SomeValue VALUES (1,6,'1000')
INSERT SomeValue VALUES (2,6,'2000')
INSERT SomeValue VALUES (3,6,'3000')
INSERT SomeTable VALUES(6,'SomeJunk')

INSERT SomeValue VALUES (1,6,'1000')
INSERT SomeValue VALUES (2,6,'2000')
INSERT SomeValue VALUES (3,6,'3000')

If you execute the proc again, you will see that you now get back the new column

T-SQL
1
EXEC prCrossTab
EXEC prCrossTab

Here is the output, you now have that additional column

TheName SomeValue Somedate SomeID SomeName SomeNumber SomeJunk
Chris 2012-01-01 Bla2 8 200 400 2000
Denis 2011-01-01 Bla 5 100 300 1000
Ted 2013-01-01 Bla3 9 300 500 3000

That is it, as you can see the PIVOT operator is pretty powerful, combined with dynamic SQL you can create a true crosstab query without even needing to know how many different values you have.

I have used the dynamic PIVOT technique to quickly copy and paste a result into Excel which had several hundred columns across and several thousand rows

Come back tomorrow for…….UNPIVOT