Dynamic Pivoting

SQL Server 2005 added the PIVOT operator which makes creating cross-tab queries very easy. However, as of this writing none of the SQL Server versions has built-in support for dynamic pivoting. The PIVOT operator only provides basic pivoting capabilities on a static list of values. In practice it is very often needed to perform this for dynamic list of values.

Here is one solution for dynamic pivoting that uses the ability in SQL Server 2005/2008 to concatenate row values using FOR XML PATH with blank element name. This method performs a query on the distinct values to pivot and creates a column list based on that. Then the resulting column list is used in a dynamic query utilizing the PIVOT operator and executed as dynamic SQL.

In this scenario the goal is to pivot order amounts by month.

-- Table with orders
CREATE TABLE Orders (
  order_id INT NOT NULL PRIMARY KEY,
  order_date DATETIME NOT NULL
             DEFAULT CURRENT_TIMESTAMP,
  amount DECIMAL(8, 2) NOT NULL DEFAULT 0
         CHECK (amount >= 0));
 
-- Sample data
INSERT INTO Orders 
(order_id, order_date, amount)
SELECT 1, '20070101', 10.50
UNION ALL
SELECT 2, '20070126', 12.50
UNION ALL
SELECT 3, '20070130', 12.00
UNION ALL
SELECT 4, '20070214', 13.75
UNION ALL
SELECT 5, '20070220', 10.00
UNION ALL
SELECT 6, '20070306', 15.00
UNION ALL
SELECT 7, '20070310', 17.50
UNION ALL
SELECT 8, '20070329', 20.00;
 
-- Build list of column values to pivot
DECLARE @cols NVARCHAR(1000);
SELECT @cols =
STUFF((SELECT N'],[' + year_month
       FROM (SELECT DISTINCT CONVERT(NCHAR(7), order_date, 126)
             FROM Orders) AS O(year_month)
       ORDER BY year_month
       FOR XML PATH('')
      ), 1, 2, '') + N']';
 
-- Build dynamic SQL query for pivoting   
DECLARE @sql NVARCHAR(2000);
SET @sql =
N'SELECT order_year, ' + @cols +
N'FROM (SELECT DATEPART(yyyy, order_date) AS order_year, ' +
             N'CONVERT(NCHAR(7), order_date, 126) AS year_month, ' +
             N'amount ' +
      N'FROM Orders) AS O ' +
N'PIVOT ' +
N'(SUM(amount) FOR year_month IN (' + @cols + N')) AS P;';
 
EXEC(@sql);
 
/*
 
Results:
 
order_year  2007-01  2007-02  2007-03
----------- -------- -------- --------
2007        35.00    23.75    52.50
 
*/

This query can be further improved by using the QUOTENAME function to prevent any possibility of SQL injection.