Need advice on SELECT query with multiple rows that should become columns

Question

I have a table:

id     data-type       data-answer
-----------------------------------
1      car             honda
1      color           yellow
1      engine          gasoline
2      car             bmw
2      color           black
3      engine          diesel

Need advice, how to write SELECT so that it would be in output:

id       car        color        engine
-----------------------------------------
1        honda      yellow       gasoline
2        bmw        black        diesel

Data in tables are to simplify the example. Have tried to search over the internet for 2 days. No solution found. Need guidance what to search.

Answer 1

You need conditional aggregation either you can do PIVOT :

select id, 
       max(case when data-type = 'car' then data_answer end) as car,
       max(case when data-type = 'color' then data_answer end) as color,
       max(case when data-type = 'engine' then data_answer end) as engine
from table t
group by id;