csv - What is the opposite of GROUP_CONCAT in MySQL?

Question

I seem to come against this problem a lot, where I have data that's formatted like this:

+----+----------------------+
| id | colors               |
+----+----------------------+
| 1  | Red,Green,Blue       |
| 2  | Orangered,Periwinkle |
+----+----------------------+

but I want it formatted like this:

+----+------------+
| id | colors     |
+----+------------+
| 1  | Red        |
| 1  | Green      |
| 1  | Blue       |
| 2  | Orangered  |
| 2  | Periwinkle |
+----+------------+

Is there a good way to do this? What is this kind of operation even called?

Answer 1

You could use a query like this:

SELECT
  id,
  SUBSTRING_INDEX(SUBSTRING_INDEX(colors, ',', n.digit+1), ',', -1) color
FROM
  colors
  INNER JOIN
  (SELECT 0 digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) n
  ON LENGTH(REPLACE(colors, ',' , '')) <= LENGTH(colors)-n.digit
ORDER BY
  id,
  n.digit

Please see fiddle here. Please notice that this query will support up to 4 colors for every row, you should update your subquery to return more than 4 numbers (or you should use a table that contains 10 or 100 numbers).