Making The Numbers Table Useful
It’s not easy to find a solution to a very simple problem in MySQL: converting a comma separated list of values into rows. Oracle database users find gobs of tutorials on using REGEXP and CONNECT BY LEVEL to make this happen. MySQL doesn’t have that. So, use the numbers table from the previous post!
The transposing is made possible by (ab)using the SUBSTRING_INDEX function. I love this function. It is right up there with GROUP_CONCAT when mixing NULL and non-null strings.
I will be using comma separated values. You can use any delimiter character you like.
Bonus: This works with empty strings, strings with one value only, and empty delimiters (i.e. “my value,,previous is empty”). No extra code needed.
Making Magic Happen
SET @mycsv = …[Read more]