Steve Steve - 3 months ago 9
SQL Question

Flattened SQL query

This is in SQL Server 2014.

Without changing the table structures what is the easiest way you can suggest to change this query (output shown below)

select
a.Id, a.Sku, a.lbl,
Desc,
(select b.Valu where b.Attribute = 'rel') as 'rel',
(select b.Valu where b.Attribute = 'uom') as 'uom',
(select b.Valu where b.Attribute = 'clas') as 'clas'
from
items a
join
itemattributes b on b.id = a.id


Output:

id sku lbl desc rel uom clas
2 X111 X111-456789 red NULL NULL C
2 X111 X111-456789 red NULL Cs NULL
2 X111 X111-456789 red 3 NULL NULL
3 X222 X222-567890 white NULL NULL B
3 X222 X222-567890 white NULL Cs NULL
3 X222 X222-567890 white 2 NULL NULL
4 X333 X333-678901 blue NULL NULL C
4 X333 X333-678901 blue NULL Ea NULL
4 X333 X333-678901 blue 9 NULL NULL


To this output:

id sku lbl desc rel uom clas
2 X111 X111-456789 red 3 Cs C
3 X222 X222-567890 white 2 Cs B
4 X333 X333-678901 blue 9 Ea C

vkp vkp
Answer

You can use conditional aggregation to group by different attribute values.

select a.Id 
    , a.Sku
    , a.lbl
    , [Desc]
    , max(case when b.Attribute = 'rel' then b.Valu end) as rel
    , max(case when b.Attribute = 'uom' then b.Valu end) as uom
    , max(case when b.Attribute = 'clas' then b.Valu end) as clas
from items a    
join itemattributes b   
    on b.id = a.id
group by a.Id 
    , a.Sku
    , a.lbl
    , [Desc]