SQL: Rotate Result

I was asked many times to rotate a denormalized table like this example:

From this:

ID Type Value
--- ------- ----------
1 Dim 10
1 Colour Blu
1 Shape Square
2 Dim 15
3 Dim 10
2 Colour Red
2 Shape Triangle
3 Shape Round
3 Colour Yellow

Obtain this:

id Dim Colour Shape
--- ---------- ---------- ----------
1 10 Blu Square
2 15 Red Triangle
3 10 Yellow Round

Maybe this method has a specific name and resolution; I use this method:

SELECT id, MAX(dimension), MAX(colour), MAX(shape)
FROM (
SELECT ID,
'dimension' = CASE WHEN type = 'dim' THEN value END ,
'colour' = CASE WHEN type = 'colour' THEN value END ,
'shape' = CASE WHEN type = 'shape' THEN value END
FROM table1) AS Result
GROUP BY id

Share on Google Plus

About Vittorio Pavesi