Flattening a Table in prep for Json
<p>Maybe it's that I'm tired but this is escaping me.</p>

<p>Let's say that I want to flatten this table:</p>

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

<p>Into this query result:</p>

<pre><code>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) 
</code></pre>

<p>The table only renders four levels deep (a, b, c, d) so no dynamic sql issue.</p>

<p>Now I'd usually just use GROUP_CONCAT(CONCAT(...)) but that won't work with the Nulls present.  And maybe using coalesce somehow will solve this but... I feel pretty stupid at the moment... and I can't figure it out.</p>

<p>Unfortunately I can't use mysql json services on this installation so I need to construct the data. thanks.</p>