CODEWITHSUNDEEP
mysql
Anurag
Anurag

Reputation: 1013

Split (explode) comma delimited column values to rows

Description

Following is Table Structure:

eligibility_table

ID     COURSE_ID     BRANCH_IDS
1      501           621,622,623
1      502
1      503           625
2      501           621
2      505           650
3      500

Now, I am making new table structure as describe below and inserting course_table,branch_table through eligibility_table. So following, final output I want

course_table

ID COURSE_ID
1  501
1  502
1  503
2  501
2  505
3  500

branch_table

ID BRANCH_ID
1  621
1  622
1  623
1  625
2  621
2  650

Problem:

I am struggling to write SQL QUERY for branch_table. I want to write a query like

INSERT INTO branch_table SELECT --- from eligibility_table --

Upvotes: 4

Views: 3958

Answers (2)

peterm
peterm

Reputation: 92845

UPDATED You can do it with SQL like this

INSERT INTO branch_table (id, branch_id)
SELECT e.id, SUBSTRING_INDEX(SUBSTRING_INDEX(e.branch_ids, ',', n.n), ',', -1) branch_id
  FROM eligibility_table e CROSS JOIN 
(
   SELECT a.N + b.N * 10 + 1 n
     FROM 
    (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
   ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
    ORDER BY n
) n
 WHERE n.n <= 1 + (LENGTH(e.branch_ids) - LENGTH(REPLACE(e.branch_ids, ',', '')))
 ORDER BY id, branch_id
  • The subquery with an alias of n generates on the fly a sequence of numbers (numbers or tally table) from 1 to 100 in this particular case using UNION ALL and CROSS JOIN. Sometimes it's handy to have a real tally table in your db.
  • In outer select innermost SUBSTRING_INDEX() gets everything up to n'th element in a list and outer SUBSTRING_INDEX() extract right most part after a last delimiter effectively getting n-th element itself.
  • CROSS JOIN allows us to produce a set of rows which is a cartesian product (of 100 rows in n and all rows in eligibility_table)
  • condition in WHERE clause filters out all unnecessary rows from the resultset

Note: this query will split up to 100 branch ids. If you need more or less you can adjust a limit by editing the inner subquery

Result in branch_table:

| ID | BRANCH_ID |
------------------
|  1 |       621 |
|  1 |       622 |
|  1 |       623 |
|  1 |       625 |
|  2 |       621 |
|  2 |       650 |

Here is SQLFiddle demo

Upvotes: 4

Nanne
Nanne

Reputation: 64439

If I'm correct in that you are changeing your database structure so it is actually normalized, and this is a one time thing, I'd suggest you do this in code. Connect to the database, read your old tables and insert the new. You can add some error-checking and exceptionhandling as well!

Just take your favorite language, and then do a standard split() on that comma-separated value, and insert those values in the table. If it doesn't work you can print out what line went wrong so you can manually fix those issues. (that will be much harder in sql to accomplish)

Upvotes: 3

Related Questions