oracle 分组后多行合并为一行
工作中有需求要将如下结构的数据(为方便处理数据类型均为字符换):
ID | DATE | VALUE |
a | 2016-10-01 | 1 |
a | 2016-10-02 | 2 |
a | 2016-10-03 | 3 |
b | 2016-10-01 | 3 |
b | 2016-10-02 | 2 |
b | 2016-10-03 | 1 |
合并为如下样式:
ID | V1 | V2 | V3 |
a | 1 | 2 | 3 |
b | 3 | 2 | 1 |
SQL如下:
select t.id,
max(case(t.date) when '2016-10-01' then t.value end) as v1,
max(case(t.date) when '2016-10-02' then t.value end) as v2,
max(case(t.date) when '2016-10-03' then t.value end) as v3
from table t
group by t.id;
----------------------------------------------------------------------
执行结果很完美!!!