MySQL: split value in column to get multiple rows

MySQLselect

I have some data in a table like so:

product_id      | categories
----------------+-------------
10              | 9,12
11              | 8
12              | 11,18,5

I want a select statement that would produce this output:

product_id      | category_id
----------------+-------------
10              | 9
10              | 12
11              | 8
12              | 11
12              | 18
12              | 5

I don't know how to phrase this scenario to be able to google it.

Best Answer

What you are looking for is the inverse of a GROUP BY aggregate query using the GROUP_CONCAT. If you are willing to store the results in a temp table, I got just the thing.

First, here is the code to use you sample data in a table called prod and a temp table called prodcat to hold the results you are looking for.

use test
drop table if exists prod;
drop table if exists prodcat;
create table prod
(
  product_id int not null,
  categories varchar(255)
) engine=MyISAM;
create table prodcat
(
  product_id int not null,
  cat  int not null
) engine=MyISAM;
insert into prod values
(10,'9,12'),(11,'8'),(12,'11,18,5');
select * from prod;

Here it is loaded

mysql> use test
Database changed
mysql> drop table if exists prod;
Query OK, 0 rows affected (0.00 sec)

mysql> drop table if exists prodcat;
Query OK, 0 rows affected (0.00 sec)

mysql> create table prod
    -> (
    ->   product_id int not null,
    ->   categories varchar(255)
    -> ) engine=MyISAM;
Query OK, 0 rows affected (0.07 sec)

mysql> create table prodcat
    -> (
    ->   product_id int not null,
    ->   cat  int not null
    -> ) engine=MyISAM;
Query OK, 0 rows affected (0.06 sec)

mysql> insert into prod values
    -> (10,'9,12'),(11,'8'),(12,'11,18,5');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from prod;
+------------+------------+
| product_id | categories |
+------------+------------+
|         10 | 9,12       |
|         11 | 8          |
|         12 | 11,18,5    |
+------------+------------+
3 rows in set (0.00 sec)

mysql>

OK, you need query to put together each product_id with each category. Here it is:

select concat('insert into prodcat select ',product_id,',cat from (select NULL cat union select ',
replace(categories,',',' union select '),') A where cat IS NOT NULL;') ProdCatQueries from prod;

Here it is executed

mysql> select concat('insert into prodcat select ',product_id,',cat from (select NULL cat union select ',
    -> replace(categories,',',' union select '),') A where cat IS NOT NULL;') ProdCatQueries from prod;
+----------------------------------------------------------------------------------------------------------------------------------+
| ProdCatQueries                                                                                                                   |
+----------------------------------------------------------------------------------------------------------------------------------+
| insert into prodcat select 10,cat from (select NULL cat union select 9 union select 12) A where cat IS NOT NULL;                 |
| insert into prodcat select 11,cat from (select NULL cat union select 8) A where cat IS NOT NULL;                                 |
| insert into prodcat select 12,cat from (select NULL cat union select 11 union select 18 union select 5) A where cat IS NOT NULL; |
+----------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql>

Let me run each line by hand

mysql> insert into prodcat select 10,cat from (select NULL cat union select 9 union select 12) A where cat IS NOT NULL;
Query OK, 2 rows affected (0.07 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into prodcat select 11,cat from (select NULL cat union select 8) A where cat IS NOT NULL;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into prodcat select 12,cat from (select NULL cat union select 11 union select 18 union select 5) A where cat IS NOT NULL;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql>

OK, good. The queries work. Did the prodcat table populate properly?

mysql> select * from prodcat;
+------------+-----+
| product_id | cat |
+------------+-----+
|         10 |   9 |
|         10 |  12 |
|         11 |   8 |
|         12 |  11 |
|         12 |  18 |
|         12 |   5 |
+------------+-----+
6 rows in set (0.00 sec)

mysql>

OK Great. It has the data.

To be honest, I think SQL Server can perform all of this in a single pivot query without a handmade temp table.

I could have taken it to another level and concatenated all the queries into a single query, but the SQL would have been insanely long. If your actual query had 1000s of rows, a single MySQL would not have been practical.

Instead of running the 3 INSERT queries by hand, you could echo the 3 INSERT queries to a text file and execute it as a script. Then, you have a table with the products and categories combinations individually written.

Related Question