CODEWITHSUNDEEP
mysqlsql
WhiteCode
WhiteCode

Reputation: 43

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

I have multiple table like this :

table

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

The result should be

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

Upvotes: 1

Views: 149

Answers (1)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

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.

Upvotes: 1

Related Questions