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:

Hope this was helpful,