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.

marked as duplicate by Paul White Sep 21 '15 at 6:03

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.


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 Sep 19 '15 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 Sep 20 '15 at 22:40
  • Thanks for the hint with the information_schema. How could I use this for the example given? – tover Sep 21 '15 at 9:06

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