Posted By

ktrout on 01/03/16


Tagged

mysql unroll variable-length-list


Versions (?)

mysql unroll variable length string list


 / Published in: SQL
 

URL: http://sqlfiddle.com/#!9/d1201/4

unroll variable length string list to normalised form.

  1. -- unrolls variable length list data to normalised form
  2. -- add another cross_join if number of elements in listdata >= 100..
  3. SELECT t.id, substring_index(substring_index(t.list, ',', seq.n), ',', -1) element
  4. FROM variable_length_listdata t
  5. CROSS JOIN
  6. (
  7. SELECT a1.n * 10 + a0.n + 1 n
  8. FROM digits_v a1
  9. CROSS JOIN digits_v a0
  10. ORDER BY n
  11. ) seq
  12. WHERE seq.n <= 1 + (length(t.list) - length(REPLACE(t.list, ',', '')))
  13. ORDER BY t.id, element
  14. ;

Report this snippet  

You need to login to post a comment.