mysql unroll variable length string list


/ Published in: SQL
Save to your folder(s)

unroll variable length string list to normalised form.


Copy this code and paste it in your HTML
  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. ;

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

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.