MySql-数据透视表

Question

我有一个包含记录的数据库表(return_period)

id  ReturnPeriod    Value   Date
1   10              10X     11/1/2012
2   20              20x     11/1/2012
3   30              30x     11/1/2012
4   10              10xx    12/1/2013
5   20              20xx    12/1/2013
6   30              30y     1/1/2015
7   30              303     1/1/2015

并期望输出表如下所示:

Date      Rp10_Value    Rp20_Value  Rp30_Value
11/1/2012    10x          20x         30x
12/1/2013    10XX         20XX  
1/1/2015                              30y
1/1/2015                              303

我想要基于日期的记录(想要多条记录)。有没有办法可以针对此类需求编写查询。谢谢

Answer 1

select date,
case when rp=10 then value else null end as Rp10_Value,
case when rp=10 then value else null end as Rp20_Value,
case when rp=10 then value else null end as Rp30_Value

from
(
SELECT date, value,  ReturnPeriod
FROM Table2 where ReturnPeriod=10
union all
SELECT date, value,  ReturnPeriod
FROM Table2 where ReturnPeriod=20
union all
SELECT date, value,  ReturnPeriod
FROM Table2 where ReturnPeriod=30
) ;