VCjUWUpp

1I have some data in a table like so:
2product_id      categories
310              9,12
411              8
512              11,18,5
6
7I want a select statement that would produce this output:
8
9product_id      category_id
1010              9
1110              12
1211              8
1312              11
1412              18
1512              5
16	
17use test
18drop table if exists prod;
19drop table if exists prodcat;
20create table prod
21(
22  product_id int not null,
23  categories varchar(255)
24) engine=MyISAM;
25create table prodcat
26(
27  product_id int not null,
28  cat  int not null
29) engine=MyISAM;
30insert into prod values
31(10,'9,12'),(11,'8'),(12,'11,18,5');
32select * from prod;
33	
34mysql> use test
35Database changed
36mysql> drop table if exists prod;
37Query OK, 0 rows affected (0.00 sec)
38
39mysql> drop table if exists prodcat;
40Query OK, 0 rows affected (0.00 sec)
41
42mysql> create table prod
43    -> (
44    ->   product_id int not null,
45    ->   categories varchar(255)
46    -> ) engine=MyISAM;
47Query OK, 0 rows affected (0.07 sec)
48
49mysql> create table prodcat
50    -> (
51    ->   product_id int not null,
52    ->   cat  int not null
53    -> ) engine=MyISAM;
54Query OK, 0 rows affected (0.06 sec)
55
56mysql> insert into prod values
57    -> (10,'9,12'),(11,'8'),(12,'11,18,5');
58Query OK, 3 rows affected (0.00 sec)
59Records: 3  Duplicates: 0  Warnings: 0
60
61mysql> select * from prod;
62+------------+------------+
63| product_id | categories |
64+------------+------------+
65|         10 | 9,12       |
66|         11 | 8          |
67|         12 | 11,18,5    |
68+------------+------------+
693 rows in set (0.00 sec)
70
71mysql>
72	
73select concat('insert into prodcat select ',product_id,',cat from (select NULL cat union select ',
74replace(categories,',',' union select '),') A where cat IS NOT NULL;') ProdCatQueries from prod;
75	
76mysql> select concat('insert into prodcat select ',product_id,',cat from (select NULL cat union select ',
77    -> replace(categories,',',' union select '),') A where cat IS NOT NULL;') ProdCatQueries from prod;
78+----------------------------------------------------------------------------------------------------------------------------------+
79| ProdCatQueries                                                                                                                   |
80+----------------------------------------------------------------------------------------------------------------------------------+
81| insert into prodcat select 10,cat from (select NULL cat union select 9 union select 12) A where cat IS NOT NULL;                 |
82| insert into prodcat select 11,cat from (select NULL cat union select 8) A where cat IS NOT NULL;                                 |
83| 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; |
84+----------------------------------------------------------------------------------------------------------------------------------+
853 rows in set (0.00 sec)
86
87mysql>
88	
89mysql> insert into prodcat select 10,cat from (select NULL cat union select 9 union select 12) A where cat IS NOT NULL;
90Query OK, 2 rows affected (0.07 sec)
91Records: 2  Duplicates: 0  Warnings: 0
92
93mysql> insert into prodcat select 11,cat from (select NULL cat union select 8) A where cat IS NOT NULL;
94Query OK, 1 row affected (0.00 sec)
95Records: 1  Duplicates: 0  Warnings: 0
96
97mysql> 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;
98Query OK, 3 rows affected (0.00 sec)
99Records: 3  Duplicates: 0  Warnings: 0
100
101mysql>
102	
103mysql> select * from prodcat;
104+------------+-----+
105| product_id | cat |
106+------------+-----+
107|         10 |   9 |
108|         10 |  12 |
109|         11 |   8 |
110|         12 |  11 |
111|         12 |  18 |
112|         12 |   5 |
113+------------+-----+
1146 rows in set (0.00 sec)
115
116mysql>
117	
118DELIMITER $$
119    CREATE FUNCTION SPLIT_STRING(val TEXT, delim VARCHAR(12), pos INT) RETURNS TEXT
120    BEGIN
121        DECLARE output TEXT;
122        SET output = REPLACE(SUBSTRING(SUBSTRING_INDEX(val, delim, pos), CHAR_LENGTH(SUBSTRING_INDEX(val, delim, pos - 1)) + 1), delim, '');
123        IF output = '' THEN
124            SET output = null;
125        END IF;
126        RETURN output;
127    END $$
128
129    CREATE PROCEDURE TRANSFER_CELL()
130    BEGIN
131        DECLARE i INTEGER;
132        SET i = 1;
133        REPEAT
134            INSERT INTO products (product_id, category_id)
135            SELECT product_id, SPLIT_STRING(categories, ',', i)
136            FROM products
137            WHERE SPLIT_STRING(categories, ',', i) IS NOT NULL;
138            SET i = i + 1;
139        UNTIL ROW_COUNT() = 0
140        END REPEAT;
141    END $$
142DELIMITER ;
143
144CALL TRANSFER_CELL() ;