CSV to Rowe

Hi experts

In MySQL, how do I SELECT a CSV list as separate rows?

For example, let's say I have a clients table with 2 fields

name - mark
services - 4,6,12

How can I return 3 rows ask
Mark  4
Mark 6
Mark 12

Thank you
APD TorontoAsked:
Who is Participating?
 
PortletPaulfreelancerCommented:
Here is a way to do it:
    CREATE TABLE customers
        (`name` varchar(40), `services` varchar(60))
    ;
        
    INSERT INTO customers
        (`name`, `services`)
    VALUES
        ('mark', '4,6,12')
    ;

Open in new window

   
    SELECT 
      name
    , SUBSTRING_INDEX(SUBSTRING_INDEX(services, ',', n.digit+1), ',', -1) service
    FROM customers
    INNER JOIN (
       SELECT 0 digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3  UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
       ) n ON LENGTH(REPLACE(services, ',' , '')) <= LENGTH(services)-n.digit
    

Open in new window


    | name | service |
    |------|---------|
    | mark |       4 |
    | mark |       6 |
    | mark |      12 |

Open in new window


See: http://sqlfiddle.com/#!9/b557e0/4
1
 
PortletPaulfreelancerCommented:
You should NOT store the data that way... that's the awful truth.
0
 
theGhost_k8Database ConsultantCommented:
Despite above query from Paul works awesome for the sample dataset; I'd like to pitch in the idea I used long back when this was once a requirement. Have a look at this article of splitting delimited column values to rows.

All you need to do is change the cursor query as per your table columns "SELECT name,services from customers;" and specify delimiter ","
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.