0

i have a table with this vertical structure. The Data comes from an external company so i have do deal with it.

+----+-----------+------+--------+
| id | status_id | key  | value  |
+----+-----------+------+--------+
|  1 |       100 | key1 | value1 |
|  2 |       100 | key2 | value2 |
|  3 |       100 | key3 | value3 |
|  4 |       200 | key1 | value1 |
|  5 |       200 | key2 | value2 |
|  6 |       200 | key3 | value3 |
+----+-----------+------+--------+

Now i wanted to create a horizontal table from this data with a trigger after insert, so that i become this structure..

+-----------+--------+--------+--------+
| status_id |  key1  |  key2  |  key3  |
+-----------+--------+--------+--------+
|       100 | value1 | value2 | value3 |
|       200 | value1 | value2 | value3 |
+-----------+--------+--------+--------+

The status_id becomes unique so i tried an INSERT with ON DUPLICATE KEY but i don't know how do i have to write it that the value from key becomes the field name for the insert.

Many greetings Kevin

Kevin
  • 1

1 Answers1

0

Did you looked into Pivot Table ? It may help you.

Useful link : http://www.artfulsoftware.com/infotree/qrytip.php?id=78

Other thread with similar question : MySQL pivot table

Keiro Dev
  • 1
  • 1
  • Yes i have read this, but it does not solve my problem. I want to create a trigger and got `NEW.key` and `NEW.value` but how can i use the `NEW.key`as the fieldname for the insert, especially for the `ON DUPLICATE KEY` part. – Kevin Nov 16 '18 at 10:43