Pivots with Dynamic Columns in SQL Server 2005
Published Friday, September 14, 2007 9:28 AM
Pivots in SQL Server 2005 can rotate a table, i.e. they can turn rows into columns. PIVOTs are frequently used in reports, and they are reasonably simple to work with. However, I've seen quite a few questions about this operator. Most questions were about the column list in the PIVOT statement. This list is fixed, but many times the new columns are determined by the report at a later stage. This problem is easily solved when we mix pivots with dynamic SQL, so here is a very simple example about how to dynamically generate the pivot statement:
PIVOT allows you to turn data rows into columns. For example, if you have a table like this (I use only three months here for simplicity):
CREATE TABLE Sales ([Month] VARCHAR(20) ,SaleAmount INT)
INSERT INTO Sales VALUES ('January', 100)
INSERT INTO Sales VALUES ('February', 200)
INSERT INTO Sales VALUES ('March', 300)
SELECT * FROM SALES
Month SaleAmount
---------------- -----------
January 100
February 200
March 300
Suppose we wanted to convert the above into this:
January February March
----------- ---------- ----------
100 200 300
We can do this using the PIVOT operator, as follows:
SELECT [January]
, [February]
, [March]
FROM ( SELECT [Month]
, SaleAmount
FROM Sales
) p PIVOT ( SUM(SaleAmount)
FOR [Month]
IN ([January],[February],[March])
) AS pvt
However, in the above example, I have the column names fixed as the first three months. If I want to create a result in which the columns are dynamic (for example, they are read from a table), then I need to construct the above query dynamically. To demonstrate this let’s look at the following example:
In the first table I have the column names I want to use:
CREATE TABLE Table1 (ColId INT,ColName VARCHAR(10))
INSERT INTO Table1 VALUES(1, 'Country')
INSERT INTO Table1 VALUES(2, 'Month')
INSERT INTO Table1 VALUES(3, 'Day')
In the second table I have the data. This consists of a row identifier (tID), a column ID (ColID) that refers to the column type in Table1, and a value:
CREATE TABLE Table2 (tID INT,ColID INT,Txt VARCHAR(10))
INSERT INTO Table2 VALUES (1,1, 'US')
INSERT INTO Table2 VALUES (1,2, 'July')
INSERT INTO Table2 VALUES (1,3, '4')
INSERT INTO Table2 VALUES (2,1, 'US')
INSERT INTO Table2 VALUES (2,2, 'Sep')
INSERT INTO Table2 VALUES (2,3, '11')
INSERT INTO Table2 VALUES (3,1, 'US')
INSERT INTO Table2 VALUES (3,2, 'Dec')
INSERT INTO Table2 VALUES (3,3, '25')
Now I would like to retrieve data from these two tables, in the following format:
tID Country Day Month
----------- ---------- ---------- ----------
1 US 4 July
2 US 11 Sep
3 US 25 Dec
In other words I want to turn the data rows in Table2 into columns. If I had a fixed set of columns for the result, i.e. the columns Country, Day, and Month were fixed, I could use SQL Server 2005’s PIVOT operator in a query like:
SELECT tID
, [Country]
, [Day]
, [Month]
FROM ( SELECT t2.tID
, t1.ColName
, t2.Txt
FROM Table1 AS t1
JOIN Table2
AS t2 ON t1.ColId = t2.ColID
) p PIVOT ( MAX([Txt])
FOR ColName IN ( [Country], [Day],
[Month] ) ) AS pvt
ORDER BY tID ;
However I need to construct this query dynamically, because the column names Country, Day, and Month are specified in a table, and can be changed independently from my query. In our case these columns are given in Table1.
In the first step to generate the final pivot query I need to create the list of columns, in this case [Country], [Day], [Month].
Since there is no string concatenation aggregator in SQL (a concatenation aggregator would not be deterministic without some order restriction), and since the column names are stored in rows of a table, I need to flatten these columns into a single row or variable. There are various solutions to achieve this. One solution would be to use a query like:
DECLARE @cols NVARCHAR(2000)
SELECT @cols = COALESCE(@cols + ',[' + colName + ']',
'[' + colName + ']')
FROM Table1
ORDER BY colName
This query works both on SQL Server 2000 and 2005. It is efficient, but some may not like it because it uses the same variable (@cols) on both sides of an assignment. Another solution that works on SQL Server 2005 only is to use XML PATH.
DECLARE @cols NVARCHAR(2000)
SELECT @cols = STUFF(( SELECT DISTINCT TOP 100 PERCENT
'],[' + t2.ColName
FROM Table1 AS t2
ORDER BY '],[' + t2.ColName
FOR XML PATH('')
), 1, 2, '') + ']'
This second query (I’ve seen this posted by Peter Larsson) has the advantage that it does not use the @cols variable on the right hand side. I like this solution more, since this can be extended as a general string concatenation aggregate in more complex queries.
Both of the above queries generate, from Table1, the string: ‘[Country],[Day], [Month]’. This column list is used twice in the pivot query that we aim to construct. Once it is use in the list of columns that we want to retrieve, and once it is used as the list of values that should become columns. Having constructed this list of columns above, we can just concatenate it with the missing parts of the pivot query like:
DECLARE @query NVARCHAR(4000)
SET @query = N'SELECT tID, '+
@cols +'
FROM
(SELECT t2.tID
, t1.ColName
, t2.Txt
FROM Table1 AS t1
JOIN Table2 AS t2 ON t1.ColId = t2.ColID) p
PIVOT
(
MAX([Txt])
FOR ColName IN
( '+
@cols +' )
) AS pvt
ORDER BY tID;'
Executing this with
EXECUTE(@query)
will give us the expected result: a table that is pivoted and shows columns that were specified in a table:
tID Country Day Month
----------- ---------- ---------- ----------
1 US 4 July
2 US 11 Sep
3 US 25 Dec
