SQL Server PIVOT perhaps?

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,

Solution 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

Solution 2:

I don't have SQL Server running here at work so this may not by completely syntatically correct but one approach would be cross tabulation

SELECT name2
     , SUM(CASE WHEN name1 = 'A' THEN value END) AS A
     , SUM(CASE WHEN name1 = 'B' THEN value END) AS B
FROM table
GROUP BY name2

For variable number of columns you could use dynamic SQL:

DECLARE @sql varchar(max)
SELECT @sql = COALESCE(@sql+',','') + 'SUM(CASE WHEN nane1 = '''+name1+''' THEN value END) AS ['+name1']' FROM table

SET @sql = 'SELECT name2, '+@sql+' FROM table GROUP BY name2'

EXEC(@sql)

Solution 3:

You can use a PIVOT clause. Your query could be something like this:

WITH Source as (
    SELECT Name1, Name2, [Value]
    FROM mytable 
)

SELECT Name2, CASE WHEN A IS NOT NULL THEN A ELSE 'your string' END As A
, CASE WHEN B IS NOT NULL THEN B ELSE 'your string' END As B
FROM (
        SELECT Name2, Name1, [Value]
        FROM Source
) s
PIVOT 
(
    MAX([Value]) FOR Name1 IN (A, B) -- any other Name1 would go here
) p

using your sample data above, my results were

P1  1           3
P2  1           1
P3  2           your string
P4  your string 1

EDIT:

Since you have an unknown number of columns, you will need to look at using dynamic SQL and there are several answers here on SO about that with PIVOT.

SQL Server 2005 Pivot on Unknown Number of Columns

Pivot Table and Concatenate Columns