Perhaps SQL Server PIVOT?

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 to do this to:

  Ab
 P1 1 4
 P2 1 1
 P3 2 null
 P4 null 1

Thanks,

Answer 1

You can use PIVOT . Your query might look 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 the above data, 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 here are some answers to SO about what with PIVOT.

SQL Server 2005 position with an unknown number of columns

Pivot table and concatenation table

Answer 2

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 much status you have. It dynamically collects the request using PIVOT . The only way to make PIVOT with dynamic columns is to collect the query dynamically, which can be done in SQL Server.

Other examples:

• T-SQL Summary
• How to create a dashboard by joining the same table with SQL Server?
• fooobar.com/questions/183224 / ...

Answer 3

I don't have SQL Server working here at work, so this may not be completely correct, but one approach would be a crosstab

 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 a variable number of columns, you can 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)