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

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?

CodePudding user response：

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

for spesific column u can change it to column name,

SELECT <ColumnName> FROM <TableName>;

Ex: in my case i want show just content of name. so i can write

SELECT name FROM users;

CodePudding user response：

Try this and see if it pulls up the data you need. If the table is named TABLENAME for this example.

Select genre from TABLENAME

If you have more than one database you can set it before the TABLENAME like this:

    Select genre from DATABASENAME.TABLENAME

Unless you mean like this link here: Can I concatenate multiple MySQL rows into one field?

EDIT - Try this maybe?

SELECT DISTINCT genre from TABLENAME

Now that i think about it this won't work , the other answer seems the actual solution for this.