数据集如下:
COLUMN1 COLUMN2 COLUMN3 COLUMN4 COLUMN5 COLUMN6 COLUMN7 COLUMN8
AAA BBB CCC DDD EEE FFF GGG A1
AAA BBB CCC DDD EEE FFF GGG A2
AAA BBB CCC DDD EEE FFF GGG A3
BBB AAA DDD CCC GGG EEE FFF B1
BBB AAA DDD CCC GGG EEE FFF B2
BBB AAA DDD CCC GGG EEE FFF B3
CCC DDD FFF EEE BBB AAA GGG C1
CCC DDD FFF EEE BBB AAA GGG C2
CCC DDD FFF EEE BBB AAA GGG C3要得到的数据列表如下:
COLUMN1 COLUMN2 COLUMN3 COLUMN4 COLUMN5 COLUMN6 COLUMN7 COLUMN8
AAA BBB CCC DDD EEE FFF GGG A1,A2,A3
BBB AAA DDD CCC GGG EEE FFF B1,B2,B3
CCC DDD FFF EEE BBB AAA GGG C1,C2,C3在线急等,谢谢各位~
COLUMN1 COLUMN2 COLUMN3 COLUMN4 COLUMN5 COLUMN6 COLUMN7 COLUMN8
AAA BBB CCC DDD EEE FFF GGG A1
AAA BBB CCC DDD EEE FFF GGG A2
AAA BBB CCC DDD EEE FFF GGG A3
BBB AAA DDD CCC GGG EEE FFF B1
BBB AAA DDD CCC GGG EEE FFF B2
BBB AAA DDD CCC GGG EEE FFF B3
CCC DDD FFF EEE BBB AAA GGG C1
CCC DDD FFF EEE BBB AAA GGG C2
CCC DDD FFF EEE BBB AAA GGG C3要得到的数据列表如下:
COLUMN1 COLUMN2 COLUMN3 COLUMN4 COLUMN5 COLUMN6 COLUMN7 COLUMN8
AAA BBB CCC DDD EEE FFF GGG A1,A2,A3
BBB AAA DDD CCC GGG EEE FFF B1,B2,B3
CCC DDD FFF EEE BBB AAA GGG C1,C2,C3在线急等,谢谢各位~
SQL> with tab as
2 (
3 select 'AAA' COLUMN1,'BBB' COLUMN2,'CCC' COLUMN3,'DDD' COLUMN4,'EEE' COLUMN5,'FFF' COLUMN6,'GGG' COLUMN7,'A1' COLUMN8 from dual union all
4 select 'AAA' COLUMN1,'BBB' COLUMN2,'CCC' COLUMN3,'DDD' COLUMN4,'EEE' COLUMN5,'FFF' COLUMN6,'GGG' COLUMN7,'A2' COLUMN8 from dual union all
5 select 'AAA' COLUMN1,'BBB' COLUMN2,'CCC' COLUMN3,'DDD' COLUMN4,'EEE' COLUMN5,'FFF' COLUMN6,'GGG' COLUMN7,'A3' COLUMN8 from dual union all
6 select 'BBB' COLUMN1,'AAA' COLUMN2,'DDD' COLUMN3,'CCC' COLUMN4,'GGG' COLUMN5,'EEE' COLUMN6,'FFF' COLUMN7,'B1' COLUMN8 from dual union all
7 select 'BBB' COLUMN1,'AAA' COLUMN2,'DDD' COLUMN3,'CCC' COLUMN4,'GGG' COLUMN5,'EEE' COLUMN6,'FFF' COLUMN7,'B2' COLUMN8 from dual union all
8 select 'BBB' COLUMN1,'AAA' COLUMN2,'DDD' COLUMN3,'CCC' COLUMN4,'GGG' COLUMN5,'EEE' COLUMN6,'FFF' COLUMN7,'B3' COLUMN8 from dual union all
9 select 'CCC' COLUMN1,'DDD' COLUMN2,'FFF' COLUMN3,'EEE' COLUMN4,'BBB' COLUMN5,'AAA' COLUMN6,'GGG' COLUMN7,'C1' COLUMN8 from dual union all
10 select 'CCC' COLUMN1,'DDD' COLUMN2,'FFF' COLUMN3,'EEE' COLUMN4,'BBB' COLUMN5,'AAA' COLUMN6,'GGG' COLUMN7,'C2' COLUMN8 from dual union all
11 select 'CCC' COLUMN1,'DDD' COLUMN2,'FFF' COLUMN3,'EEE' COLUMN4,'BBB' COLUMN5,'AAA' COLUMN6,'GGG' COLUMN7,'C3' COLUMN8 from dual
12 )
13 select COLUMN1,
14 COLUMN2,
15 COLUMN3,
16 COLUMN4,
17 COLUMN5,
18 COLUMN6,
19 COLUMN7,
20 wm_concat(COLUMN8) COLUMN8
21 from tab
22 group by COLUMN1, COLUMN2, COLUMN3, COLUMN4, COLUMN5, COLUMN6, COLUMN7
23 ;COLUMN1 COLUMN2 COLUMN3 COLUMN4 COLUMN5 COLUMN6 COLUMN7 COLUMN8
------- ------- ------- ------- ------- ------- ------- --------------------------------------------------------------------------------
AAA BBB CCC DDD EEE FFF GGG A1,A2,A3
BBB AAA DDD CCC GGG EEE FFF B1,B2,B3
CCC DDD FFF EEE BBB AAA GGG C1,C3,C2SQL>
10g 支持wm_concat()函数,直接用就可以了
9i的话用sys_connect_by_path 递归构造就可以了