MSSQL 2005 Dynamic PIVOT

Here’s a method of generating a dynamic pivot of a dataset. It first generates the columns as a string (“[ColumnHeader0],[ColumnHeader1],[ColumnHeader2], ...” format), builds a dynamic SQL string and executes it using the PIVOT expression.

From the code below, the @cols variable is generated by using the FOR XML PATH expression. Based on the query, it uses the columns and data to generate an XML document (the columns being the tags for the data). If no parameter is provided for the PATH expression, “row” will be used as the root path:

<row>
  <col0>
    ColumnHeader0
  </col0>
  <col1>
    ColumnHeader1
  </col1>
  <col2>
    ColumnHeader2
  </col2>
  .
  .
  .
</row>

The default behavior is to generate an XML document, however, if you concatenate any strings to the columns, that string will be used to join the columns (instead of XML tags). In the example below, “],[” is used to “glue” the row data together, then STUFF is used to remove the first two characters of the resulting string (e.g. “],[ColumnHeader0],[ColumnHeader1],[ColumnHeader2]“) as indicated by the start and length parameters (1 and 2, respectively).

DECLARE @cols NVARCHAR(2000)
SELECT  @cols = STUFF(( SELECT DISTINCT TOP 100 PERCENT
                                '],[' + t.Col0
                        FROM dbo.the_table AS t
                        WHERE 1 = 1
                          AND Col3 = 'A'
                        ORDER BY '],[' + t.Col0
                        FOR XML PATH('')
                      ), 1, 2, '') + ']'

DECLARE @query NVARCHAR(4000)
SET @query = N'SELECT *
FROM (
  SELECT
    Col0,
    Col1,
    Col2
  FROM dbo.the_table
  WHERE 1 = 1
    AND Col3 = ''A''
)
p PIVOT (
  SUM(Col2)
  FOR [Col0]
    IN (' + @cols + ')
) pvt'

EXECUTE(@query)