Pivoting / Cross-Tabbing Data in SQL Server

I was having a discussion with someone the other day on the merits of using PIVOT / Cross-Tab functionality in SQL.

Back when the primary platform I used was Teradata, you didn’t have a PIVOT function, so if you wanted to do something similar to a Pivot, you had to build a series of CASE statements to build your fields up. This was fine, but you had to know beforehand what columns you wanted to have on your output

SQL SERVER (2005+) does provide a PIVOT option, but to be honest, it’s only marginally better than the CASE statement approach – you still have to know what column headings you want in your query.

So, if you want to pivot by the day of the week, that’s fine because there are a fixed number of days in the week and you can account for this.

But what if you want to have a field for each Agent/Region/date etc? You won’t always be able know what fields you’re going to need.

A typical PIVOT query would look something like this…

SELECT NewDate, p1.[35], p1.[36], p1.[37], p1.[38]
FROM
(SELECT NewDate,RegionID, COUNT(AgentID) AS ItemCount FROM
(SELECT CONVERT(DATE,ReceivedDateTime) AS NewDate, RegionID, AgentID FROM dbo.[Delivery])
q1 GROUP BY NewDate, RegionID) AS q2
PIVOT
( SUM(ItemCount) FOR RegionID IN ([35],[36],[37],[38])) AS p1;

Where you know the store numbers you want to pivot against.

But, what if we have hundreds of agents across multiple regions, and I don’t want to paste them all in?

Thankfully, there is a workaround. Not very pretty, but does the job!

We can utilise building a dynamic query – that is, a query string that is built on the fly each time it’s run. The query itself being ran is the same format as above – we just build it in a different way.

DECLARE @ColumnHeadings NVARCHAR(MAX), @FinalQuery NVARCHAR(MAX);

--Build our string of field headings
SET @ColumnHeadings = N'';
SELECT @ColumnHeadings += N', p1.' + QUOTENAME(RegionID)
 FROM (SELECT DISTINCT RegionID FROM [dbo].[Delivery]) AS Regions;

--Build our actual query
SET @FinalQuery = N'
SELECT NewDate, ' + STUFF(@ColumnHeadings, 1, 2, '') + '
FROM
(SELECT NewDate,RegionID, COUNT(AgentID) AS AgentCount FROM
(SELECT CONVERT(DATE,ReceivedDateTime) AS NewDate, RegionID, AgentID FROM [dbo].[Delivery])
q1 GROUP BY NewDate, RegionID) AS q2
PIVOT
( SUM(AgentCount) FOR RegionID IN (' + STUFF(REPLACE(@ColumnHeadings, ', p1.[', ',['), 1, 1, '') + ')) AS p1;';
PRINT @FinalQuery; -- use this to see what the final code looks like - useful for debugging
EXEC sp_executesql @FinalQuery;

Here, I have a query to populate a variable (@ColumnHeadings) with our list of region numbers. (The QUOTENAME() function takes a list, and turns it into a string with a deliminator)

This variable is then forced (‘STUFFED’) into the regular PIVOT query as a the variable @FinalQuery

It’s this output query that is then executed.

OK, so it’s not one for everyday use – Excel will do the job faster, but a useful approach if you need to do it.