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

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

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

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

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

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

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

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

What if we want this instead?

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

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.

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

EXEC prCrossTab

output

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

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

EXEC prCrossTab

Here is the output, you now have that additional column

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