行列转换

[DannyHau]

初始结果是这样：
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

求救。。。。

[lhdz_bj]

最好写个函数喽

[sweet低调的华丽]

这个属于行列转换喽：列转行，行转列，不过万变不离其宗。 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; 仅供参考

[sych888]

11G 用PIVOT 建测试表，插入测试数据 create table test(id int,name varchar(20),quarter int,profile int) 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