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.
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.
SELECT
d, 1 as h, h1 as 'value'
FROM t
UNION ALL
SELECT
d, 2 as h, h2 as 'value'
FROM t
UNION ALL
SELECT
d, 3 as h, h3 as 'value'
FROM t
# optionally you can add
ORDER BY d, h, value
