How to turn a single row to multiple row in mysql?

Question

ex:

the column 'genre' has a row of data:

+----------------------------+
|            genres          |
+----------------------------+
|   action, adventure, drama |       |
|                            |
+----------------------------+

my desired output will be a column that holds the following data:

+------------+
|    genre   | 
+------------+
| action     |
| adventure  | 
| drama      | 
+------------+

how do I do it in mysql?

score 0 · Accepted Answer · answered Feb 12 '22 at 04:06

You can try to create a split function as below to get your expectation part of values by comma.

Schema (MySQL v5.7)

DELIMITER $$
DROP FUNCTION IF EXISTS `SPLIT_STR` $$
CREATE FUNCTION `SPLIT_STR`(
  x VARCHAR(255),
  delim VARCHAR(12),
  pos INT
) RETURNS varchar(255) CHARSET latin1
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
       LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
       delim, '') $$

DELIMITER ;

then use UNION ALL to combine those result as below

Query #1

SELECT SPLIT_STR(genres, ',', 1) genre  FROM T 
UNION ALL
SELECT SPLIT_STR(genres, ',', 2) FROM T
UNION ALL
SELECT SPLIT_STR(genres, ',', 3) FROM T;

genre
action
adventure
drama

View on DB Fiddle

NOTE

I would suggest you re-design your table scheme because it was bad for store value by comma and you need to convert as rows.

How to turn a single row to multiple row in mysql?

1 Answers1