How to Transpose SQL Server Data Using PIVOT

The PIVOT operator in SQL Server allows you to convert rows into columns, transforming data from a vertically oriented form to a horizontally oriented form. This can be useful when seeking to summarize data based on multiple attributes.

Example Data and Desired Output

Consider the following starting dataset:

SELECT Name1, Name2, Value FROM mytable

Name1 | Name2 | Value
------- ------- ------
A      | P1     | 1
A      | P2     | 1
A      | P3     | 2
B      | P1     | 3
B      | P2     | 1
B      | P4     | 1

The desired output is to pivot the data so that the PIVOT operator converts the Name1 column values into column headers, and the values from the Value column are summarized by the Name2 column.

Transposing Data with PIVOT in SQL Server 2005

For SQL Server 2005, the PIVOT operator can be used in the following manner:

DECLARE @cols VARCHAR(1000)
DECLARE @sqlquery VARCHAR(2000)

SELECT  @cols = STUFF(( SELECT distinct  ','   QuoteName([Name1])
                        FROM myTable FOR XML PATH('') ), 1, 1, '') 


SET @sqlquery = 'SELECT * FROM
      (SELECT Name2, Name1, Value
       FROM myTable ) base
       PIVOT (Sum(Value) FOR [Name1]
       IN ('   @cols   ')) AS finalpivot'

EXECUTE ( @sqlquery )

This query dynamically generates a PIVOT query based on the distinct values in the Name1 column, assembling the column headers and summarizing the data accordingly. Ultimately, the result is the transposed data in the desired format:

P1      P2      P3      P4
------- ------- ------- -------
1       1       2       NULL
3       1       NULL    1