Loading...

Make multiple rows from one, in mySQL


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    |
+-------+------------+------+
- - Source

Answers

answered 1 week ago Gihan Gamage #1

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;

answered 1 week ago DxTx #2

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

comments powered by Disqus