6

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.

2
5

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.

2
  • Wow, I was thinking there was some built-in MySQL trickery that could accomplish this... but, I appreciate your hard work. thanks! I'll wait to see if someone can come up with a more clever approach. – Sparctus Jan 30 '14 at 20:47
  • 1
    The problem is that you should not store values like '9,12' in one column from the beginning. It is too less structured for Structured Query Language. ;-) – flaschenpost May 3 '14 at 19:17
3

There isn't a built-in MySQL trickery per se, but you can store a custom procedure that will accomplish your goal in a clever way. Assuming your products table has the columns product_id, categories, and the new category_id:

DELIMITER $$
    CREATE FUNCTION SPLIT_STRING(val TEXT, delim VARCHAR(12), pos INT) RETURNS TEXT
    BEGIN
        DECLARE output TEXT;
        SET output = REPLACE(SUBSTRING(SUBSTRING_INDEX(val, delim, pos), CHAR_LENGTH(SUBSTRING_INDEX(val, delim, pos - 1)) + 1), delim, '');
        IF output = '' THEN
            SET output = null;
        END IF;
        RETURN output;
    END $$

    CREATE PROCEDURE TRANSFER_CELL()
    BEGIN
        DECLARE i INTEGER;
        SET i = 1;
        REPEAT
            INSERT INTO products (product_id, category_id)
            SELECT product_id, SPLIT_STRING(categories, ',', i)
            FROM products
            WHERE SPLIT_STRING(categories, ',', i) IS NOT NULL;
            SET i = i + 1;
        UNTIL ROW_COUNT() = 0
        END REPEAT;
    END $$
DELIMITER ;

CALL TRANSFER_CELL() ;

Afterwards, I would delete all rows WHERE categories NOT NULL.

1
  • From the original question and the first answer, you would presumably be writing this to a second table (a temporary table, ideally), as the original table was still supposed to be around in its original form. That said, good answer! – RDFozz Nov 22 '17 at 17:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.