CODEWITHSUNDEEP
sqlsql-server
Gopal B
Gopal B

Reputation: 11

SQL Server: convert rows to columns without pivot and grouping

Originally I have a SQL Server table like below:

Result  Type    Type Order  Type Value
--------------------------------------
KM1    Color    1             Blue
KM1    Shape    2             Square
KM1    Size     3             10
KM3    Color    1             Blue
KM3    Shape    2             Square
KM3    Size     3             10
KM2    Color    1             Red
KM2    Shape    2             Round
KM2    Size     3             20
KM2    Color    1             Blue
KM2    Shape    2             Square
KM2    Size     3             10
KM2    Color    1             Blue
KM2    Shape    2             Round
KM2    Size     3             10

and my expected result should be like

Color   Shape   Size    Result
-------------------------------------
Blue    Square   10     KM1, KM3, KM2
Red     Round    20     KM2
Blue    Round    10     KM2

Could this be done? I don't think the pivot will help as I have same result for the multiple combination of values.

Upvotes: 0

Views: 394

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

This is two levels of aggregation. The first is to describe each result. The second is to get the keys together. So:

with t as (
      select result,
             max(case when type = 'Color' then value end) as color,
             max(case when type = 'Size' then value end) as size,
             max(case when type = 'Shape' then value end) as shape
      from t
      group by result
     )
select color, size, shape,
       stuff( (select ',' + t2.result
               from t t2
               where t2.color = t.color and t2.size = t.size and t2.shape = t.shape
               for xml path ('')
              ), 1, 1, '') as keys
from (select distinct color, size, shape
      from t
     ) t;

Upvotes: 2

Related Questions