mysql - Prepared Statement: How to join header -

mysql - Prepared Statement: How to join header -

i've got 2 tables want bring together header , first column within prepared statement. i've got managed bring together column, how bring together header?

table1 :

id |name ---------- 1 | 2 | b 3 | c 4 | d 5 | e

table2 :

teama|teamb|won|lost -------------------- 1 | 2 | 5 | 3 1 | 3 | 2 | 4 1 | 4 | 9 | 1 2 | 5 | 5 | 5 3 | 1 | 2 | 4

result-matrix:

| | b | c | d | e ---------------------------- | 0 | 2 | -2 | 8 | 0 b | 0 | 0 | 0 | 0 | 0 c | -2 | 0 | 0 | 0 | 0

sql fiddle

in order team names ids, have bring together on table twice.

if know values, static version of code is:

select a.name teama, max(case when b.name = 'a' won - lost else 0 end) a, max(case when b.name = 'b' won - lost else 0 end) b, max(case when b.name = 'c' won - lost else 0 end) c, max(case when b.name = 'd' won - lost else 0 end) d, max(case when b.name = 'e' won - lost else 0 end) e yourtable t left bring together teams on t.teama = a.id left bring together teams b on t.teamb = b.id grouping a.name;

see sql fiddle demo.

then if using prepared statement create dynamically, code be:

set @sql = null; select group_concat(distinct concat( 'max(case when teamb = ''', teamb, ''' won - lost else 0 end) `', teamb, '`' ) ) @sql ( select a.name teama, b.name teamb, t.won, t.lost yourtable t left bring together teams on t.teama = a.id left bring together teams b on t.teamb = b.id order teamb ) x; set @sql = concat('select teama, ', @sql, ' ( select a.name teama, b.name teamb, t.won, t.lost yourtable t left bring together teams on t.teama = a.id left bring together teams b on t.teamb = b.id ) s grouping teama'); prepare stmt @sql; execute stmt; deallocate prepare stmt;

see sql fiddle demo

the result is:

| teama | | b | c | d | e | ------------------------------ | | 0 | 2 | 0 | 8 | 0 | | b | 0 | 0 | 0 | 0 | 0 | | c | -2 | 0 | 0 | 0 | 0 |

mysql join prepared-statement pivot