Split comma separated values from table A to column in table B

Question

Let's say I have the following table A:

id  type  customer_id
---------------------
1   A     456,484,234
2   B     235,659,456
3   C     664,976,345
4   D     453,975,547
5   E     764,986,457

Now let's say that I have a table B with the following columns:

id  type  customerid

I would like to extract the type and customer_id from table A and put it in table be but split all the customer_ids into their own rows like so:

id  type  customerid
--------------------
1   A     456
2   A     484
3   A     234
4   B     235
5   B     659
6   B     456
...
and so on.... You get the idea.

I know this can be done with PHP but I'm looking for strictly an SQL way of doing this.

I don't understand how the result set ids relate to the data set ids — Strawberry, Aug 13 '19 at 20:10

spencer7593 · Answer 1 · 2019-08-13T20:15:03.107

let's try using the SUBSTRING_INDEX function

SELECT SUBSTRING_INDEX(t.customer_id,',',1) AS c1
     , SUBSTRING_INDEX(t.customer_id,',',2) AS c2
     , SUBSTRING_INDEX(t.customer_id,',',3) AS c3
     , SUBSTRING_INDEX(t.customer_id,',',4) AS c4
  FROM ( SELECT 1 AS id, 'A' AS `type`, '456,484,234' AS customer_id
          UNION ALL SELECT 2, 'B', '235,659,456'
       ) t

returns

c1      c2       c3           c4
------  -------  -----------  -------------
456     456,484  456,484,234  456,484,234
235     235,659  235,659,456  235,659,456

let's try adding another SUBSTRING_INDEX with -1 parameter

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(t.customer_id,',',1),',',-1) AS c1
     , SUBSTRING_INDEX(SUBSTRING_INDEX(t.customer_id,',',2),',',-1) AS c2
     , SUBSTRING_INDEX(SUBSTRING_INDEX(t.customer_id,',',3),',',-1) AS c3
     , SUBSTRING_INDEX(SUBSTRING_INDEX(t.customer_id,',',4),',',-1) AS c4
  FROM ( SELECT 1 AS id, 'A' AS `type`, '456,484,234' AS customer_id
          UNION ALL SELECT 2, 'B', '235,659,456'
       ) t

returns

c1    c2    c3    c4
----  ----  ----  ----
456   484   234   234
235   659   456   456

let's append an extra comma before we do the SUBSTRING_INDEX

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(t.customer_id,','),',',1),',',-1) AS c1
     , SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(t.customer_id,','),',',2),',',-1) AS c2
     , SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(t.customer_id,','),',',3),',',-1) AS c3
     , SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(t.customer_id,','),',',4),',',-1) AS c4
  FROM ( SELECT 1 AS id, 'A' AS `type`, '456,484,234' AS customer_id
          UNION ALL SELECT 2, 'B', '235,659,456'
       ) t

c1    c2    c3   c4      
----  ----  ---- ----
456   484   234             
235   659   456

lets generate separate rows, and discard rows that have empty string:

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(t.customer_id,','),',',i.n),',',-1) AS cn
  FROM ( SELECT 1 AS id, 'A' AS `type`, '456,484,234' AS customer_id
          UNION ALL SELECT 2, 'B', '235,659,456'
       ) t
 CROSS
  JOIN ( SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 ) i
HAVING cn <> ''

returns

we can add expressions to the SELECT list

SELECT t.id 
     , t.type
     , SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(t.customer_id,','),',',i.n),',',-1) AS cn
  FROM ( SELECT 1 AS id, 'A' AS `type`, '456,484,234' AS customer_id
          UNION ALL SELECT 2, 'B', '235,659,456'
       ) t
 CROSS
  JOIN ( SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 ) i
HAVING cn <> ''
 ORDER BY t.id, t.type, i.n

returns

id  type   cn      
--  -----  ----
 1  A      456
 1  A      484
 1  A      234
 2  B      235
 2  B      659
 2  B      456

to generate sequential values for the id, we can wrap that query as an inline view, and use user-defined variables to generate values

SELECT (@id := @id + 1) AS id
     , v.type
     , v.cn 
  FROM (
         SELECT t.id 
              , t.type
              , SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(t.customer_id,','),',',i.n),',',-1) AS cn
           FROM ( SELECT 1 AS id, 'A' AS `type`, '456,484,234' AS customer_id
                   UNION ALL SELECT 2, 'B', '235,659,456'
                ) t
          CROSS
           JOIN ( SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 ) i
         HAVING cn <> ''
         ORDER BY t.id, t.type, i.n
       ) v
 CROSS
  JOIN ( SELECT @id := 0 ) j

returns

id  type   cn
--  -----  ----
 1  A      456
 2  A      484
 3  A      234
 4  B      235
 5  B      659
 6  B      456

Wait a minute, why do you have hardcoded "456,484,234" in your query? These values could be anything. — user765368, Aug 13 '19 at 20:40
just replace the inline view `FROM ( SELECT ... ) t` with a reference to your table (of unknown name) `FROM mytable t` . Using an inline view (as demonstrated in this answer) is convenient way to test with a large variety of "could be anything" values, especially edge cases and corner cases which do not appear in our table. Of course, we could setup a demo table, with `CREATE TABLE`, `INSERT` our rows, run our tests, and then drop the table. But no other information about the table was provided in the question. An inline view is convenient. — spencer7593, Aug 13 '19 at 21:44
`( SELECT 1 AS id, 'A' AS `type`, '456,484,234' AS customer_id UNION ALL SELECT 2,'B','235,659,456' UNION ALL SELECT 3,'C','664,976,345' UNION ALL SELECT 4,'D','453,975,547' UNION ALL SELECT 5,'E','764,986,457' UNION ALL SELECT 997,'xx','' UNION ALL SELECT 998,'xy',',,,,' UNION ALL SELECT 999,'xz',',,555,,666,,' )` — spencer7593, Aug 13 '19 at 21:52

Split comma separated values from table A to column in table B

1 Answers1