I have a table that looks like this (d could be the day, and h1 h2 h3 the values of the single hours of the day(assuming the day has 3 hours)):

d  h1 h2 h3
1  1  2  3 
2  4  5  6
3  7  8  9

and I what an output like this (one row with all the hours):

d h value
1 1 1
1 2 2
1 3 3
2 1 4
2 2 5
2 3 6
3 1 7
3 2 8
3 3 9

Is there a way do this with MySQL?

Edit: Having to address every single column in the syntax should be avoided, because this is supposed to also work for a lot of columns.


1 Answer 1


What you describe is more like "turn columns into rows". You can do this with UNION [ALL].

The difference between UNION and UNION ALL is, that UNION does not return duplicates, it acts like DISTINCT. If you know, there won't be duplicates, you might as well use UNION ALL and spare your server the work to detect any.

d, 1 as h, h1 as 'value'
d, 2 as h, h2 as 'value'
d, 3 as h, h3 as 'value'
# optionally you can add
ORDER BY d, h, value
  • Very interesting solution! Is there also a way to do this, if there is one column per minute of the day instead of just 3 columns?
    – tover
    Commented Sep 19, 2015 at 21:33
  • You mean you have 1440 columns? One column per minute of the day? If yes, then I'd suggest that you better find a solution to overcome the bad database design, instead of doing crazy queries. If it's an emergency, you can build a query by querying the information_schema database.
    – tombom
    Commented Sep 20, 2015 at 22:40
  • Thanks for the hint with the information_schema. How could I use this for the example given?
    – tover
    Commented Sep 21, 2015 at 9:06

Not the answer you're looking for? Browse other questions tagged or ask your own question.