CODEWITHSUNDEEP
sqlsql-server
anjulis
anjulis

Reputation: 229

SQL: Pivoting on more than one column

I have a table

Name |  Period |  Value1 |  Value2
-----+---------+---------+-------
 A       1        2        3
 A       2        5        4
 A       3        6        7
 B       1        2        3
 B       2        5        4
 B       3        6        7

I need results like

Name | Value1 |  Value2
-----+--------+------
 A   | 2,5,6  |  3,4,7
 B   | 2,5,6  |  3,4,7

Number of periods is dynamic but I know how to handle it so, for simplicity, let's say there are 3 periods

The query below gives me results for Value1. How can I get results for both? I can always do them separately and then do a join but the table is really big and I need "combine" four values, not two. Can I do it in one statement?

SELECT Name,
[1]+','+ [2] + ','+ [3]  ValueString
FROM   (
 select  Name,  period,   cpr from #MyTable     
 ) as s     
  PIVOT(SUM(Value1)        
   FOR period IN ([1],[2],[3])

Upvotes: 0

Views: 65

Answers (2)

Mansoor
Mansoor

Reputation: 4192

Using Group By with stuff function and get expected result

SELECT Name ,  STUFF((SELECT ',' + CAST(Value1 AS VARCHAR) FROM #MyTable T2 WHERE T1.Name = T2.Name FOR XML PATH('')),1,1,'') Value1
        ,  STUFF((SELECT ',' + CAST(Value2 AS VARCHAR) FROM #MyTable T3 WHERE T1.Name = T3.Name FOR XML PATH('')),1,1,'') Value2 FROM #MyTable T1 GROUP BY Name

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269513

Use conditional aggregation. Combining the values into strings is a bit tricky, requiring XML logic in SQL Server:

select n.name,
       stuff((select ',' + cast(value1 as varchar(max))
              from t
              where n.name = t.name
              order by t.period
              for xml path ('')
             ), 1, 1, ''
            ) as values1,
       stuff((select ',' + cast(value2 as varchar(max))
              from t
              where n.name = t.name
              order by t.period
              for xml path ('')
             ), 1, 1, ''
            ) as values2
from (select distinct name
      from t
     ) n;

Your values look like numbers, hence the explicit cast and the lack of concern for XML special characters.

You may ask why this does the distinct in a subquery rather than in the outer query. If done in the outer query, then the SQL engine will probably do the aggregation for every row before doing the distinct. I'm not sure if the optimizer is good enough run the subqueries only once per name.

Upvotes: 4

Related Questions