MySQL how change columns and rows in table?

Question

I have got next table:

date            3 6 9 12 15 18 21 24     
2015.01.01      3 2 5  2  5  7  2  4
2015.01.02      9 3 1  3  4  6  4  1
2015.01.03      0 2 3  1  5  6  3  4

I need to get table like:

date                  time          numbers
2015.01.01            03:00:00       3
2015.01.01            06:00:00       2
2015.01.01            09:00:00       5
2015.01.01            12:00:00       2
2015.01.01            15:00:00       5
2015.01.01            18:00:00       7
2015.01.01            21:00:00       2
2015.01.01            24:00:00       4
2015.01.02            03:00:00       9
2015.01.02            06:00:00       3

How I can do it?

Add the columnnames of original table

avk
– avk

2015-03-10 18:47:07 +00:00
Commented Mar 10, 2015 at 18:47 — avk
– avk, Commented Mar 10, 2015 at 18:47

sgeddes · Accepted Answer · 2015-03-10 19:13:23Z

1

Here's one approach with union all:

select `date`, '3:00:00' as `time`, `3` as val from yourtable
union all select `date`, '6:00:00' as `time`, `6` as val from yourtable
union all select `date`, '9:00:00' as `time`, `9` as val from yourtable
...

SQL Fiddle Demo

BTW, I'd highly recommend renaming your columns...

Here's another approach using conditional aggregation and a cross join:

select `date`, val as `time`, 
  max(case when val = 3 then `3`
           when val = 6 then `6`
           when val = 9 then `9`
      end) as `val`
from yourtable cross join (select 3 val union all select 6 union all select 9) t
group by `date`, val

More Fiddle

answered Mar 10, 2015 at 19:13

sgeddes

62.9k7 gold badges67 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL how change columns and rows in table?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related