rows to columns mysql

Question

I have a table with 1000 cities that report 3 environmental variables (variable1, variable2, variable3) on day1...day30. Here is an example table.

create table myTable1 (dt date, City varchar(15), type varchar(10), 
                       Day1 int, Day2 int, Day3 int);

insert into myTable1 values
('2014-09-19','Toronto','Variable1', 100,90,80),
('2014-09-19','Toronto','Variable2', 20,15,10),
('2014-09-19','Toronto','Variable3', 3,2,1),
('2014-09-19','Chicago','Variable1', 999,888,777),
('2014-09-19','Chicago','Variable2', 500,400,300),
('2014-09-19','Chicago','Variable3', 300,250,200);

I want to manipulate it so the final result is in the following structure. I want a new column, Day that takes the value of the day (i.e. 1, 2 ... 30) and the values inside the column named type to become columns (so 3 columns called Variable1, Variable2, Variable3).

dt, City, Day, Variable1, Variable2, Variable3
2014-09-19, Toronto, 1, 100, 20, 3
2014-09-19, Toronto, 2, 90, 15,2
2014-09-19, Toronto, 3, 80, 10, 1
2014-09-19, Chicago, 1, 999,500,300
2014-09-19, Chicago, 2, 888,400, 250
2014-09-19, Chicago, 3, 777,300,200

I know I can do this in sql server as per the solution in unpivot row values into multple columns. Since mySQL doesn't have pivot/unpivot I'm not sure how to do this. I have tinkered with some code in the following fiddle demo but it doesn't quite do what I want it to do. I appreciate your help.

http://sqlfiddle.com/#!2/91176/2

Answer 1

This is a complicated conditional aggregation:

select t.dt, t.city, d.day,
       max(case when d.day = 1 and t.type = 'Variable1' then day1
                when d.day = 2 and t.type = 'Variable1' then day2
                when d.day = 3 and t.type = 'Variable1' then day3
           end) as Variable1,
       max(case when d.day = 1 and t.type = 'Variable2' then day1
                when d.day = 2 and t.type = 'Variable2' then day2
                when d.day = 3 and t.type = 'Variable2' then day3
           end) as Variable2,
       max(case when d.day = 1 and t.type = 'Variable3' then day1
                when d.day = 2 and t.type = 'Variable3' then day2
                when d.day = 3 and t.type = 'Variable3' then day3
           end) as Variable3                
from mytable1 t cross join
     (select 1 as day union all select 2 union all select 3) d
group by t.dt, t.city, d.day;

You can see this work in your SQL Fiddle.