今天又有朋友讨论以下行列转换问题:
编码 行 值
AA 1 345
BB 1 456
CC 1 657
DD 2 546
EE 2 434
FF 2 897
GG 3 566 要实现如下转换
1 2 3
AA345 DD546 GG566
BB456 EE434
CC657 FD897
关键点是行1,2，3有若干个，不是固定是3个。。
create table test (a1 varchar2(10), a2 number(3), a3 varchar2(6));
insert into test values('AA',1,'345');
insert into test values('BB',1,'456');
insert into test values('CC',1,'657');
insert into test values('DD',2,'546');
insert into test values('EE',2,'434');
insert into test values('FF',2,'897');
insert into test values('GG',3,'566');
insert into test values('EE',1,'457');
commit;
SQL> select * from test;
A1 A2 A3
---------- ---------- ------
AA 1 345
BB 1 456
CC 1 657
DD 2 546
EE 2 434
FF 2 897
GG 3 566
EE 1 457
with atest as
(
select icol*10+irow icol,irow, decode(icol,1,ivalue) c1 ,decode(icol,2,ivalue) c2, decode(icol,3,ivalue) c3
from (
select a2 icol, a1||a3 ivalue, row_number() over(partition by a2 order by a1||a3) irow from test)
)
select irow , ltrim(rtrim(max(sys_connect_by_path(c1,' ')))) c1,
ltrim(rtrim(max(sys_connect_by_path(c2,' ')))) c2,
ltrim(rtrim(max(sys_connect_by_path(c3,' ')))) c3 from atest
connect by icol-10 = prior icol
group by irow
SQL> col c1 for a10;
SQL> col c2 for a10;
SQL> col c3 for a10;
SQL> select irow , ltrim(rtrim(max(sys_connect_by_path(c1,' ')))) c1,
2 ltrim(rtrim(max(sys_connect_by_path(c2,' ')))) c2,
3 ltrim(rtrim(max(sys_connect_by_path(c3,' ')))) c3 from
4 (
5 select icol*10+irow icol,irow, decode(icol,1,ivalue) c1 ,decode(icol,2,ivalue) c2, decode(icol,3,ivalue) c3
6 from (
7 select a2 icol, a1||a3 ivalue, row_number() over(partition by a2 order by a1||a3) irow from test)
8 )
9 connect by icol-10 = prior icol
10 group by irow
11 ;
IROW C1 C2 C3
---------- ---------- ---------- ----------
1 AA345 DD546 GG566
2 BB456 EE434
3 CC657 FF897
4 EE457
Oracle10g 中, 还有函数 WMSYS.WM_CONCAT 可以使用, 如
SQL> WITH A AS (
2 SELECT 1123 ID,'2008-9-19' DAY,'9:7:47' TIME FROM DUAL UNION
3 SELECT 1123 ID,'2008-9-19' DAY,'19:7:47' TIME FROM DUAL UNION
4 SELECT 1123 ID,'2008-9-29' DAY,'13:7:47' TIME FROM DUAL UNION
5 SELECT 1123 ID,'2008-9-29' DAY,'12:7:47' TIME FROM DUAL )
6 select ID,DAY,WMSYS.WM_CONCAT(TIME) TIME FROM A GROUP BY ID,DAY;
ID DAY TIME
---------- --------- --------------------
1123 2008-9-19 19:7:47,9:7:47
1123 2008-9-29 12:7:47,13:7:47
