By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE category ( ID INT, TITLE CHAR(3), CATEGORY TEXT);
INSERT INTO category VALUES
( 1 , 'aaa' , 'design, home, clothing'),
( 2 , 'bbb' , 'asian, indian '),
( 3 , 'ccc' , 'second hand');
SELECT * FROM category;
Records: 3 Duplicates: 0 Warnings: 0
| ID | TITLE | CATEGORY |
|---|---|---|
| 1 | aaa | design, home, clothing |
| 2 | bbb | asian, indian |
| 3 | ccc | second hand |
SELECT category.ID, category.TITLE, TRIM(jsontable.value) CATEGORY
FROM category
CROSS JOIN JSON_TABLE( CONCAT('["', REPLACE(category.CATEGORY, ',', '","'), '"]'),
"$[*]" COLUMNS( value VARCHAR(254) PATH "$" )
) AS jsontable
ORDER BY category.ID, jsontable.value;
| ID | TITLE | CATEGORY |
|---|---|---|
| 1 | aaa | clothing |
| 1 | aaa | home |
| 1 | aaa | design |
| 2 | bbb | indian |
| 2 | bbb | asian |
| 3 | ccc | second hand |