Merge multipe rows into one row

Question

I have 2 tables:

TBL_SERVICE:
ID    SERVICE_NAME
--    ------------
1     servicea
2     serviceb
3     servicec

TBL_OPTIONS:
ID    TYPE     VALUE     ID_SERVICE
--    ----     -----     ----------
1     AAA      X         1
2     BBB      Y         1
3     CCC      Z         1
4     AAA      S         2
5     BBB      X         2
6     AAA      T         3

now i need to generate rows like this:

ID_SERVICE   TYPE_AAA  TYPE_BBB  TYPE_CCC
----------   --------  --------  --------
1            X         Y         Z   
2            S         X        
3            T

currently i do this using php code but i need to do it in sql query. is it possible in mysql?

Answer 1

You can use conditional aggregation:

select id_service,
       max(case when type = 'AAA' then value end) as type_aaa,
       max(case when type = 'BBB' then value end) as type_bbb,
       max(case when type = 'CCC' then value end) as type_ccc
from t
group by id_service;