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., p1., p1., p1. 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 (,,,)) 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.