0

I have a table like this:

Datetime Milan London
YY/MM/DD 302 267
YY/MM/DD 54 108

and I'm trying to alter the table to have the column names as values in a column named "city" and the values in the cities column in a new column named "wind_direction"

Datetime City wind _direction
YY/MM/DD Milan 302
YY/MM/DD Milan 54
YY/MM/DD London 267
YY/MM/DD London 108

I tried looking in ALTER TABLE syntax but nothing helped me

Steve
  • 1
  • 1
  • 1
    Some good examples here (I answered, but I will delete as the answers at this link do a better job of explaining and giving options)? [MySQL - How to unpivot columns to rows?](https://stackoverflow.com/questions/15184381/mysql-how-to-unpivot-columns-to-rows) – JNevill Jun 21 '23 at 20:10
  • Another idea might be to use concat and look up the information schema so that you can use that to loop through all the dynamic column names to then feed that into your insert statement for each row. But JNevill's recommendation is best since it seems a built in system like this exists already. – easleyfixed Jun 21 '23 at 20:12
  • agreed although it is nice to also see that the table has many columns but the typed version is still superior – easleyfixed Jun 21 '23 at 20:13
  • I used the union all and it worked, thank you very much guys. – Steve Jun 21 '23 at 20:34

0 Answers0