初始结果是这样:
AAAAAA,9,201301,LATE PAY
AAAAAA,9,201302,LATE PAY
AAAAAA,9,201303,LATE PAY
BBBBBB,9,201301,LATE PAY
BBBBBB,9,201302,LATE PAY
BBBBBB,9,201303,LATE PAY
如何得到这样的结果?
AAAAAA,9,201301,9,201302,9,201303,LATE PAY
BBBBBB,9,201301,9,201302,9,201303,LATE PAY求救。sql
AAAAAA,9,201301,LATE PAY
AAAAAA,9,201302,LATE PAY
AAAAAA,9,201303,LATE PAY
BBBBBB,9,201301,LATE PAY
BBBBBB,9,201302,LATE PAY
BBBBBB,9,201303,LATE PAY
如何得到这样的结果?
AAAAAA,9,201301,9,201302,9,201303,LATE PAY
BBBBBB,9,201301,9,201302,9,201303,LATE PAY求救。sql
SELECT COL1,COL2,SUM(DECODE(COL3,'201301','201301')),
COL2,SUM(DECODE(COL3,'201302','201302')),
COL2,SUM(DECODE(COL3,'201303','201303')),COL4
FROM TEST
GROUP BY COL1,COL4,COL2
ORDER BY COL1;
仅供参考
insert into test values(1,''a'',1,1000)
insert into test values(1,''a'',2,2000)
insert into test values(1,''a'',3,4000)
insert into test values(1,''a'',4,5000)
insert into test values(2,''b'',1,3000)
insert into test values(2,''b'',2,3500)
insert into test values(2,''b'',3,4200)
insert into test values(2,''b'',4,5500) select * from test
id name quarter profile
----------- -------------------- ----------- -----------
1 a 1 1000
1 a 2 2000
1 a 3 4000
1 a 4 5000
2 b 1 3000
2 b 2 3500
2 b 3 4200
2 b 4 5500 (8 row(s) affected) 利用PIVOT将个季度的利润转成横向显示: select id,name,
[1] as "一季度",
[2] as "二季度",
[3] as "三季度",
[4] as "四季度"
from
test
pivot
(
sum(profile)
for quarter in
([1],[2],[3],[4])
)
as pvt