Advanced Search

Return to Snippet

Revision: 70283
at January 3, 2016 13:43 by ktrout
Initial Code 
-- unrolls variable length list data to normalised form
-- add another cross_join if number of elements in listdata >= 100..
select t.id, substring_index(substring_index(t.list, ',', seq.n), ',', -1) element
from variable_length_listdata t 
cross join
(
  select a1.n * 10 + a0.n + 1 n
  from digits_v a1
  cross join digits_v a0
  order by n
) seq
where seq.n <= 1 + (length(t.list) - length(replace(t.list, ',', '')))
order by t.id, element
;
Initial URL 
http://sqlfiddle.com/#!9/d1201/4
Initial Description 
unroll variable length string list to normalised form.
Initial Title 
mysql unroll variable length string list
Initial Tags 
mysql
Initial Language 
SQL