Make multiple rows from one, in mySQL

Question

I cant find any solution to do this in mySQL. Is there any funciton for this?

I tried to do this with SUBSTRING_INDEX, but I cant solve with it.

My table looks like this:

+----+-------+--------------+
| id | group | work_place   |
+----+-------+--------------+
| 1  |  TTL  | ERG PT TT RK |
+----+----------------------+
| 2  |  PFF  | ER PT TL KK  |
+----+-------+--------------+

And I need this:

+-------+------------+------+
| group | work_place | No.  |
+-------+------------+------+
|  TTL  |     ERP    | 1    |
+-------+------------+------+
|  TTL  |     PT     | 2    |
+-------+------------+------+
|  TTL  |     TT     | 3    |
+-------+------------+------+
|  TTL  |     RK     | 4    |
+-------+------------+------+
|  PFF  |     ER     | 1    |
+-------+------------+------+
|  PFF  |     PT     | 2    |
+-------+------------+------+
|  PFF  |     TL     | 3    |
+-------+------------+------+
|  PFF  |     KK     | 4    |
+-------+------------+------+

That's why one should **never** store serialized datas in a RDBMS. Think about normalization — Cid, Apr 15 '19 at 12:42
_My table looks like this.._ This is a very bad db design... think about [normalizing](https://www.studytonight.com/dbms/database-normalization.php) your data — B001ᛦ, Apr 15 '19 at 12:43
You can't do this natively in MySQL, but you'll find some ideas [here](https://stackoverflow.com/questions/17942508/sql-split-values-to-multiple-rows) — Thomas G, Apr 15 '19 at 12:45
I'm not the owner of the db. I can't normalize it. My job is to create a view from it, which is very hard in this situation. — zolee519, Apr 15 '19 at 12:47
It would be simpler to normalize, and create a view from it for the current format. — Paul Spiegel, Apr 15 '19 at 12:52
Is there any table, which contains all possible values for `work_place` — Paul Spiegel, Apr 15 '19 at 12:55
How many values in work_place column? Is it dynamic or is there a fixed value? — DxTx, Apr 15 '19 at 13:20
https://stackoverflow.com/questions/1096679/can-mysql-split-a-column — Paul Spiegel, Apr 15 '19 at 13:37

score 0 · Answer 1 · answered Apr 15 '19 at 13:15

How about this(this is just idea may be someone can improve this with sql functions),

insert into your_table_name (group,work_place) 
SELECT group,SUBSTRING_INDEX(work_place, ' ', 1)  FROM  your_table_name;
insert into your_table_name (group,work_place) 
SELECT group,SUBSTRING_INDEX(SUBSTRING_INDEX(work_place,' ', 2), ' ',-1) FROM  your_table_name;
insert into your_table_name (group,work_place) 
SELECT group,SUBSTRING_INDEX(SUBSTRING_INDEX(work_place,' ', 3), ' ',-1) FROM  your_table_name;
insert into your_table_name (group,work_place) 
SELECT group,SUBSTRING_INDEX(work_place, ' ', -1) FROM  your_table_name;

DxTx · Answer 2 · 2019-04-15T15:50:26.140

Maybe something like this... Just an idea...
If you have more than 5 values in the work_place column, then this won't work.
However, you can edit the query as needed to support more values.
Anyway, as suggested in the comments, this is not a good database design.

SELECT id, group, col
FROM   (SELECT id,
               group,
               SUBSTRING_INDEX(work_place, " ", 1) AS col
        FROM   tablename
        WHERE  SUBSTRING_INDEX(work_place, " ", 1) != work_place

        UNION ALL

        SELECT id,
               group,
               SUBSTRING_INDEX(SUBSTRING_INDEX(work_place, " ", 2), " ", -1) AS col
        FROM   tablename
        WHERE  SUBSTRING_INDEX(SUBSTRING_INDEX(work_place, " ", 2), " ", -1) != work_place

        UNION ALL

        SELECT id,
               group,
               SUBSTRING_INDEX(SUBSTRING_INDEX(work_place, " ", 3), " ", -1) AS col
        FROM   tablename
        WHERE  SUBSTRING_INDEX(SUBSTRING_INDEX(work_place, " ", 3), " ", -1) != work_place

        UNION ALL

        SELECT id,
               group,
               SUBSTRING_INDEX(SUBSTRING_INDEX(work_place, " ", 4), " ", -1) AS col
        FROM   tablename
        WHERE  SUBSTRING_INDEX(SUBSTRING_INDEX(work_place, " ", 4), " ", -1) != work_place

        UNION ALL

        SELECT id,
               group,
               SUBSTRING_INDEX(SUBSTRING_INDEX(work_place, " ", 5), " ", -1) AS col
        FROM   tablename
        WHERE  SUBSTRING_INDEX(SUBSTRING_INDEX(work_place, " ", 5), " ", -1) != work_place) tmp
GROUP  BY id, group, col

Online Example: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=b5842162ab0f51f831f3996e9ceae91c

Make multiple rows from one, in mySQL

2 Answers2