Dynamic Rows to Columns

Summary

Do you have a situation where you have data in your table rows that you want to transpose into columns? Furthermore, you are not sure of the number of columns you want to transpose?

Read on below for a straightforward approach on how to use the Sql Server PIVOT statement combined with dynamic SQL to easily transpose any number of columns.

Consider that you have a table with a variable number of columns called MyColumn. Then consider a table which has an entry for every column where it links some data to the column.

You want to transpose the resultset so that the columns in the rows become acutal columns. The trick here is to understand that you will need to build a dynamic query so that you can pass in a variable number of columns.

Preparation

-- Create the sample tables
CREATE TABLE MyColumn([ID] VARCHAR(255), [Name] VARCHAR(255))
CREATE TABLE MyDynamicData([RowID] INT, [ColumnID] VARCHAR(255), [Data] VARCHAR(255))

-- Create the dynamic columns
INSERT INTO MyColumn([ID], [Name]) SELECT 'A', 'Column A'
INSERT INTO MyColumn([ID], [Name]) SELECT 'B', 'Column B'
INSERT INTO MyColumn([ID], [Name]) SELECT 'C', 'Column C'
INSERT INTO MyColumn([ID], [Name]) SELECT 'D', 'Column D'

-- Create data which is linked to columns
DECLARE        @counter INT
DECLARE @rows INT
SET @counter = 1
SET @rows = 1000
WHILE (@counter < @rows)
BEGIN
    INSERT INTO MyDynamicData([RowID], [ColumnID], [Data]) SELECT    @counter, 'A', 'Col A Row ' + CAST(@counter AS VARCHAR) + ' Data'
    INSERT INTO MyDynamicData([RowID], [ColumnID], [Data]) SELECT    @counter, 'B', 'Col B Row ' + CAST(@counter AS VARCHAR) + ' Data'
    INSERT INTO MyDynamicData([RowID], [ColumnID], [Data]) SELECT    @counter, 'C', 'Col C Row ' + CAST(@counter AS VARCHAR) + ' Data'
    INSERT INTO MyDynamicData([RowID], [ColumnID], [Data]) SELECT    @counter, 'D', 'Col D Row ' + CAST(@counter AS VARCHAR) + ' Data'
    SET @counter = @counter + 1
END

SELECT * FROM MyColumn
SELECT * FROM MyDynamicData 

Pivoting the data

DECLARE @cols NVARCHAR(2000)
SELECT  @cols = STUFF(( SELECT DISTINCT TOP 100 PERCENT
                                '],[' + [Name]
                        FROM    MyColumn
                        ORDER BY '],[' + [Name]
                        FOR XML PATH('')
                      ), 1, 2, '') + ']'

DECLARE @sql VARCHAR(2000)
SET @sql = 'SELECT [RowID], ' + @cols +
' FROM  
(
SELECT A.[RowID], B.[Name], A.[Data]
FROM MyDynamicData AS A
INNER JOIN
MyColumn AS B
ON A.[ColumnID]=B.[ID]
GROUP BY A.[RowID], B.[Name], A.[Data]
) AS SourceTable
PIVOT
(
    MAX([Data])
    FOR [Name] IN (' + @cols + ')
) AS PivotTable
'
EXEC (@sql)

The resulting data will be correctly pivoted.

Reference: http://msdn.microsoft.com/en-us/library/ms177410.aspx

Keywords: SQL Server 2010 2005 PIVOT TRANSPOSE