SQL Server PIVOT perhaps?

Question

SELECT Name1, Name2, Value FROM mytable gives me the following result set:

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

How do I translate that to:

       A     B
P1     1     4
P2     1     1
P3     2     null
P4     null  1

Thanks,

Answer 1

Since you are using SQL Server 2005, here is the code:

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 will work no matter how many different status you have. It dynamically assembles a query with PIVOT. The only way you can do PIVOT with dynamic columns is by assembling the the query dynamically, which can be done in SQL Server.

Other examples:

• Pivot data in T-SQL
• How do I build a summary by joining to a single table with SQL Server?
• https://stackoverflow.com/q/8248059/570191