Join multiple tables and show table's row as column with value in same table

Question

I have multiple table like this :

I want the result like in the picture if I use 'where table1.id = 1' :

• table2 is store value and key of columns name

• table3 is store columns name that I need to show

The result should be

ID | Name | A | B | C
1  | a | 10 | 20 | 30

Answer 1

You need little JOINs with conditional aggrgation :

select t1.id, t1.name, 
       sum(case when t3.name = 'a' then t2.value else 0 end) as A,
       sum(case when t3.name = 'b' then t2.value else 0 end) as B,
       sum(case when t3.name = 'c' then t2.value else 0 end) as C
from table1 t1 inner join 
     table2 t2
     on t2.table1_id = t1.id inner join
     table3 t3
     on t3.key_id = t2.key
where t1.id = 1
group by t1.id, t1.name;

However, this would do aggregation with only known values prior, if you want go with dynamic way, then you might need programming approach in MySQL.