CODEWITHSUNDEEP
sqlsql-serverdatabase
dog2bert
dog2bert

Reputation: 83

Flatten table hierarchy

I want to transform a table like this:

Parent  Child   Value
1       Color   Red
1       Height  11
1       Width   12
1       Length  11
2       Color   Blue
2       Height  10
2       Width   2
2       Length  5

Into this:

Parent  Color   Height  Width   Length
1       Red     11      12      11
2       Blue    10      2       5

The child attributes could change at anytime so having the converted columns dynamically built would be best.

Upvotes: 2

Views: 160

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93694

Use Conditional Aggregate to transpose the rows to column

select Parent,
max(case when Child='Color' then Value End) 'Color',
max(case when Child='Height' then Value End) 'Height',
max(case when Child='Width' then Value End) 'Width',
max(case when Child='Length' then Value End) 'Length'
from yourtable
group by Parent

Upvotes: 3

Related Questions