mysql查询结果翻转,如何在MySql中旋转查询

i've the following table:

Year, Name, Revenue, Qty

I would like a result table with

Name, Revenue2012, Qty2012, Revenue2013, Qty2013

How to do in Sql for MySql ?

Thanks

解决方案

MySQL doesn't have a PIVOT function but you can get the result using an aggregate function with a CASE expression:

select name,

sum(case when year = 2012 then revenue else 0 end) revenue2012,

sum(case when year = 2012 then qty else 0 end) qty2012,

sum(case when year = 2013 then revenue else 0 end) revenue2013,

sum(case when year = 2013 then qty else 0 end) qty2013

from yourtable

group by name