CODEWITHSUNDEEP
mysqlsqldatabase
user3631879
user3631879

Reputation: 25

how to get the data using comma separator from mysql

I have table, in this table each row contains some columns and each column contains comma separated values like:

 column1   |  column2            | column3
 ------------------------------------------
 1,2,3,4,5 | abc,xyz,pqr,std,bca |  etc.

Now I need to get each row converted into 5 rows such as: 

1st row like col1 1 col2 abc col3 ........
2nd row like col1 2 col2 xyz col3.........
3st row like col1 3 col2 pqr col3 ........
4nd row like col1 4 col2 std col3.........
5st row like col1 5 col2 bca col3 ........

How can I achieve this?

Upvotes: 2

Views: 122

Answers (1)

Kickstart
Kickstart

Reputation: 21513

It is possible but messy.

SELECT DISTINCT SUBSTRING_INDEX(SUBSTRING_INDEX(column1, ',', units.i + tens.i * 10), ',', -1) AS col1,
        SUBSTRING_INDEX(SUBSTRING_INDEX(column2, ',', units.i + tens.i * 10), ',', -1) AS col2,
        SUBSTRING_INDEX(SUBSTRING_INDEX(column3, ',', units.i + tens.i * 10), ',', -1) AS col3,
        SUBSTRING_INDEX(SUBSTRING_INDEX(column4, ',', units.i + tens.i * 10), ',', -1) AS col4
FROM sometable
CROSS JOIN (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9)units
CROSS JOIN (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9)tens

Assuming that all the columns for a row contain the same number of comma separated fields, this will cope with up to 100 comma separated fields.

I am using DISTINCT to eliminate the duplicates (with the way substring_index works, if there are less than 100 comma separated values then the last one will be repeated until there are 100), but you probably want your original tables unique key in the select as well to avoid legitimate duplicates. An alternative is to count the number of comma separated values in a field in a row and add a where clause to check the generated number against this count.

Probably a really bad idea to do this in live code, but might be worthwhile if you are doing a conversion of an existing table into a new format for future use.

Upvotes: 1

Related Questions