Pivots with Dynamic Columns in SQL Server 2005/2008
Pivots in SQL Server 2005/2008 can convert row into column data. Pivots are frequently used in reports, and are reasonably easy to work with.
However, many people have asked me how to make the column list dynamic. Normally, this list is fixed, but many times the new columns are determined by the data 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 query like this:
USE AdventureWorks
GO
SELECT * FROM
(SELECT CustomerID, DATEPART(m, OrderDate) OrderMonth, SubTotal
FROM Sales.SalesOrderHeader
WHERE OrderDate between '20030101' and '20031231'
and CustomerID IN (2,4,6,7,8,9)) src
PIVOT (SUM(SubTotal) FOR OrderMonth
IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) AS pvt
GO
The rows coming from the internal query are transposed into colums:
SELECT * FROM
(SELECT CustomerID, DATEPART(m, OrderDate) OrderMonth, SubTotal
FROM Sales.SalesOrderHeader
WHERE OrderDate between '20030101' and '20031231'
and CustomerID IN (2,4,6,7,8,9)) src
PIVOT (SUM(SubTotal) FOR OrderMonth
IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) AS pvt
This is ok if the colum list suppied in the IN clause is fixed like the 12 months of the year. But if the list evolves with the data, you would have to re-write your pivot. Not to practical.
Let’s look at another example:
SELECT * FROM
(SELECT CustomerID, YEAR(OrderDate) OrderYear, SubTotal
FROM Sales.SalesOrderHeader
WHERE CustomerID >=1 and CustomerID <=35) src
PIVOT (SUM(SubTotal) FOR OrderYear
IN ([2002],[2003])) AS pvt
GO
In this pivot example, we want to display a colums for each year and a row for each customer:
Notice that we are only displaying data for years 2002 and 2003. We could modify our pivot to make it dynamic.
Look at the following code:
DECLARE @listCol VARCHAR(2000)
SELECT @listCol = STUFF(( SELECT DISTINCT
'],[' + ltrim(str(YEAR(OrderDate)))
FROM Sales.SalesOrderHeader
ORDER BY '],[' + ltrim(str(YEAR(OrderDate)))
FOR XML PATH('')
), 1, 2, '') + ']'
By using the combination of STUFF and FOR XML PATH we can build a string that concatenates all columns by doing a SELECT DISTINCT on our table before doinf the pivot.
After executing this piece of code, you should get in the @listCol variable the list of colums needed to do the pivot.
Now, all we have to do is to convert our pivot into a dynamic query:
DECLARE @listCol VARCHAR(2000)
DECLARE @query VARCHAR(4000)
SELECT @listCol = STUFF(( SELECT DISTINCT
'],[' + ltrim(str(YEAR(OrderDate)))
FROM Sales.SalesOrderHeader
ORDER BY '],[' + ltrim(str(YEAR(OrderDate)))
FOR XML PATH('')
), 1, 2, '') + ']'
SET @query =
'SELECT * FROM
(SELECT CustomerID, YEAR(OrderDate) OrderYear, SubTotal
FROM Sales.SalesOrderHeader
WHERE CustomerID >=1 and CustomerID <=35) src
PIVOT (SUM(SubTotal) FOR OrderYear
IN ('+@listCol+')) AS pvt'
EXECUTE (@query)
Et voila:
