Convert many column into multiple columns and rows in mysql

Question

is there any way to convert many columns into row in mysql?

Table looks like this:

+----+---------+-----------+-----------+-------------+-------------+-----------+-----------+
| id | style   | process_1 | process_2 | time_proc_1 | time_proc_2 | machine_1 | machine_2 |
+----+---------+-----------+-----------+-------------+-------------+-----------+-----------+
|  1 | WW102   | abcdefghi | jklmnopqr |      0.1    |      0.2    |   HF-101  |   HF-102  |
|  2 | 70AAG76 | abcdefghi | jklmnopqr |      0.1    |      0.2    |   HF-101  |   HF-102  |
|  3 | 37542851| jklmnopqr | stuvwxyz0 |      0.2    |      0.3    |   HF-102  |   HF-103  |
+----+---------+-----------+-----------+-------------+-------------+-----------+-----------+

already trying using this query

SELECT style,'process_1' AS process, process_1 AS process_desc FROM process_table
UNION ALL
SELECT style,'process_2' AS process, process_2 AS process_desc FROM process_table
UNION ALL
SELECT style,'time_proc_1' AS time, time_proc_1 AS time_min FROM process_table
UNION ALL
SELECT style,'time_proc_2' AS time, time_proc_1 AS time_min FROM process_table
UNION ALL
SELECT style,'machine_1' AS machine, machine_1 AS machine_type FROM process_table
UNION ALL
SELECT style,'machine_2' AS machine, machine_2 AS machine_type FROM process_table
ORDER BY style

the result is :

+---------+-------------+-------------+
|  style  |  process    | process_desc|
+---------+-------------+-------------+
| WW102   | process_1   | abcdefghi   |
| WW102   | process_2   | jklmnopqr   |
| WW102   | time_proc_1 |    0.1      |
| WW102   | time_proc_2 |    0.2      |
| WW102   | machine_1   |   HF-101    |
| WW102   | machine_2   |   HF-102    |
| 70AAG76 | process_1   | abcdefghi   |
| 70AAG76 | process_2   | jklmnopqr   |
| 70AAG76 | time_proc_1 |    0.1      |
| 70AAG76 | time_proc_2 |    0.2      |
| 70AAG76 | machine_1   |   HF-101    |
| 70AAG76 | machine_2   |   HF-102    |
| 37542851| process_1   | jklmnopqr   |
| 37542851| process_2   | stuvwxyz0   |
| 37542851| time_proc_1 |    0.2      |
| 37542851| time_proc_2 |    0.3      |
| 37542851| machine_1   |   HF-102    |
| 37542851| machine_2   |   HF-103    |
+---------+-------------+-------------+

Result i looking for is look like this:

+---------+-----------+-----------+---------+
|  style  |  process  | time_proc | machine |
+---------+-----------+-----------+---------+
| WW102   | abcdefghi |    0.1    |  HF-101 |
| WW102   | jklmnopqr |    0.2    |  HF-102 |
| 70AAG76 | abcdefghi |    0.1    |  HF-101 |
| 70AAG76 | jklmnopqr |    0.2    |  HF-102 |
| 37542851| jklmnopqr |    0.2    |  HF-102 |
| 37542851| stuvwxyz0 |    0.3    |  HF-103 |
+---------+-----------+-----------+---------+

i only have limited knowledge in mysql, so any help was very appreciated. Thank You

There's no pretty way. What you have is probably as best its going to get. — danblack
– danblack, Commented Mar 6, 2020 at 4:39

Akina · Accepted Answer · 2020-03-06 05:22:58Z

1

SELECT style, process_1 process, time_proc_1 time_proc, machine_1 machine
FROM process_table
UNION ALL
SELECT style, process_2, time_proc_2, machine_2
FROM process_table

answered Mar 6, 2020 at 5:22

Akina

43.2k6 gold badges17 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Convert many column into multiple columns and rows in mysql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related