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,

@jadarnel27 - There's a hint in the title of the question ;) — Tony, Aug 24 '11 at 20:58
I'm using SQL Server 2005. Sure there is a hint, but I don't know how to pivot it which is why I asked :) — user683302, Aug 24 '11 at 20:59
A and B are not fixed, the top select could return more than just A and B, sometimes it will be A, B, C, ... X for instance. — user683302, Aug 24 '11 at 21:02
@jadarnel27 wasn't your fault, the question wasn't tagged with [tag:sql-server-2005] until Tony edited it, and just because the OP happened to use the keyword `PIVOT` doesn't necessarily mean they know it's only available in 2005+. Lots of people ask HOW DO I PIVOT THIS? when they're using SQL Server 2000. :-) — Aaron Bertrand, Aug 24 '11 at 21:18
@user683302 I think that's important information that should be part of the question - it's not clear that there may be other values other than `A` and `B`. Also what happens when there are two rows like this: `A, P1, 1` and `A, P1, 4`? Detailing the unique constraints on the table will help others determine how to aggregate if necessary. — Aaron Bertrand, Aug 24 '11 at 21:22

score 10 · Answer 1 · edited May 23 '17 at 12:04

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:

This resembled a post similiar - http://stackoverflow.com/questions/6849678/how-do-i-build-a-summary-by-joining-to-a-single-table-with-sql-server/6849795#6849795 in fact the injection attack I mentioned :). — JonH, Nov 23 '11 at 20:02

Aaron Silverman · Answer 2 · 2011-08-24T21:30:00.083

4

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)

edited Aug 24 '11 at 21:30

answered Aug 24 '11 at 21:21

Aaron Silverman

22,070
21
83
103

2

There are much [more constructive](http://xkcd.com/303/) things you can do while your code is compiling ;) I posted that comment while I was at work as well haha – Josh Darnell Aug 25 '11 at 14:13

score 2 · Accepted Answer · edited May 23 '17 at 11:55

2

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

edited May 23 '17 at 11:55

Community

1
1

answered Aug 24 '11 at 21:20

Taryn

242,637
56
362
405

The issue is the OP cannot limit the output to A, B. There may also be C, ..., X, as he stated in one of the comments. – Aaron Bertrand Aug 24 '11 at 21:21
I made a few modifications and was able to get it to work by using this example and http://stackoverflow.com/questions/159456/pivot-table-and-concatenate-columns-sql-problem#159803. One last thing, how would I go about replacing the NULLs with another String. I tried a CASE but it only replaces the 'Value' fields that have values, not the NULLs – user683302 Aug 24 '11 at 23:02
I updated the above PIVOT query using CASE and is changes the NUll values to a string. – Taryn Aug 24 '11 at 23:10
I would prefer COALESCE over CASE in this situation, otherwise it's nice. – Andriy M Aug 24 '11 at 23:46
@Andriy I use COALESCE too in a lot of situations. I have gotten flack for using it on here before so I used CASE this time. – Taryn Aug 24 '11 at 23:48

SQL Server PIVOT perhaps?

3 Answers3

Linked