行到列mysql [英] rows to columns mysql

问题描述

我有一张表格，其中有1000个城市，它们在day1 ... day30报告3个环境变量(变量1，变量2，变量3).这是一个示例表.

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);

我想对其进行操作，因此最终结果是以下结构.我想要一个新列 Day ，它采用day的值(即1、2 ... 30)和名为 type 的列中的值成为列(所以3列称为Variable1，Variable2，Variable3).

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

我知道我可以按照中的解决方案中的解决方案在sql server中执行此操作进入多列.由于mySQL没有枢轴/不枢轴，因此我不确定如何执行此操作.在下面的小提琴演示中，我已经修改了一些代码，但是它并没有完全实现我想要的功能.我感谢您的帮助.

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

推荐答案

这是一个复杂的条件聚合:

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;

您可以在SQL Fiddle中看到这项工作.

You can see this work in your SQL Fiddle.

这篇关于行到列mysql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！