mysql - Flattening a Table in prep for Json -

maybe it's i'm tired escaping me.

let's want flatten table:

a_id   a_val  b_id   b_val  c_id   c_val  d_id   d_val 1           10     b      100    c      1000   f    1           20     d      200    g      null   null  2      e      30     h      300         null   null    2      j      40     k      null   null   null   null  3      l      null   null   null   null   null   null 

into query result:

id mystring  1, (1:a,10:b,100:c,1000:f),(1:a,20:d,200:g)  2, (2:e,30:h,300:i),(2:j,40:k)  3, (3:l)  

the table renders 4 levels deep (a, b, c, d) no dynamic sql issue.

now i'd use group_concat(concat(...)) won't work nulls present. , maybe using coalesce somehow solve but... feel pretty stupid @ moment... , can't figure out.

unfortunately can't use mysql json services on installation need construct data. thanks.

the solution here combination of clever concatenation , ifnull calls. shot in dark:

select a_id, group_concat(concat('(',     a_id, ':', a_value, ',',      ifnull(b_id, ''), if(b_id not null, ':', ''), ifnull(b_val, ''),     ...repeat c , d    ')' ) separator ',') table group a_id;