Converting a 50+ column table into 1 much smaller table I'm currently dealing with a legacy PLC device which is spitting out data in an annoying MYSQL table. The table structure is:

PK | FK | FK | FK | Result1 | Result2 | ... | ResultN


As you can imagine, performing any sort of query or join on this is quite cumbersome to write and takes a long time to resolve.

I'd like to setup an automatic select -> insert statement to pull the data and put it into a more appropriate table

PK | FK | FK | FK | Result Number | Result Value


Unfortunately, I'm stuck on how to pull the column names so I know what to populate the Result Number column with, as well as then how to cross-multiply across the table to make sure each new row has all the required keys for each value.
You need N INSERT ... SELECT ... statements.  A one-time tedious task.