--参考:
create table t2(col1 varchar2(10),col2 varchar2(10));
insert into t2 values('001','vl1');
insert into t2 values('001','vl2');
insert into t2 values('001','vl3');
insert into t2 values('002','vl1');
insert into t2 values('002','vl2'); SELECT COL1, LTRIM(MAX(SYS_CONNECT_BY_PATH(COL2, ',')), ',') COL2
FROM (SELECT COL1,
COL2,
MIN(COL2) OVER(PARTITION BY COL1) COL2_MIN,
(ROW_NUMBER() OVER(ORDER BY COL1, COL2)) +
(DENSE_RANK() OVER(ORDER BY COL1)) NUMID
FROM T2)
START WITH COL2 = COL2_MIN
CONNECT BY NUMID - 1 = PRIOR NUMID
GROUP BY COL1 /*
COL1 COL2
---------- ----------------------------------------
001 vl1,vl2,vl3
002 vl1,vl2
2 rows selected
*/
create table t2(col1 varchar2(10),col2 varchar2(10));
insert into t2 values('001','vl1');
insert into t2 values('001','vl2');
insert into t2 values('001','vl3');
insert into t2 values('002','vl1');
insert into t2 values('002','vl2'); SELECT COL1, LTRIM(MAX(SYS_CONNECT_BY_PATH(COL2, ',')), ',') COL2
FROM (SELECT COL1,
COL2,
MIN(COL2) OVER(PARTITION BY COL1) COL2_MIN,
(ROW_NUMBER() OVER(ORDER BY COL1, COL2)) +
(DENSE_RANK() OVER(ORDER BY COL1)) NUMID
FROM T2)
START WITH COL2 = COL2_MIN
CONNECT BY NUMID - 1 = PRIOR NUMID
GROUP BY COL1 /*
COL1 COL2
---------- ----------------------------------------
001 vl1,vl2,vl3
002 vl1,vl2
2 rows selected
*/
SQL> select * from t;PHONENUM DISCOUNTRULE BEGINDATE USR
---------- ------------ -------------------- --------------------
a a1 xxx 001
b b1 xxx 002
a a2 xxx 002
b b1 xxx 001SQL>
SQL> SELECT phonenum,
2 MAX(decode(rn, 1, newfld, NULL)) ||
3 MAX(decode(rn, 2, ',' ||newfld, NULL)) ||
4 MAX(decode(rn, 3, ',' || newfld, NULL)) str
5 FROM (
6 SELECT phonenum,discountRule||','||begindate||','||usr as newfld,
7 row_number() over(PARTITION BY phonenum ORDER BY discountRule) AS rn FROM t
8 ) t
9 GROUP BY phonenum
10 ORDER BY 1;PHONENUM STR
---------- --------------------------------------------------------------------------------
a a1,xxx,001,a2,xxx,002
b b1,xxx,002,b1,xxx,001SQL>
select 'a' a, 'a1' b, 'xxx' c , ' 001' d from dual
union
select 'b' a, 'b1' b, 'xxx' c , ' 002' d from dual
union
select 'a' a, 'a1' b, 'xxx' c , ' 001' d from dual
union
select 'a' a, 'a2' b, 'xxx' c , ' 002' d from dual
union
select 'b' a, 'b1' b, 'xxx' c , ' 001' d from dual
)select replace(replace(wmsys.wm_concat(b||'/'||c||'/'||d),',',';'),'/',',')
from t
group by a
你的例子是多行合并啊
多行合并,10G可以用WMSYS.WM_CONCAT
9i用SYS_CONNECT_BY_PATH如果是固定行列转换,就用max(decode)+group
不固定行列转换
看此贴里大乌龟的回答
http://topic.csdn.net/u/20080416/11/910e40c1-60f1-441f-8b0f-19a969d30f77.html
SQL> select * from t;PHONENUM DISCOUNTRULE BEGINDATE ENDDATE USR
---------- ------------ -------------------- -------------------- --------------------
7914400 a1 20080901 20080920 001
7914400 a2 20080901 20080921 002
7914400 a3 20080901 20080930 001
7915500 b1 20080901 20080912 001
7915500 b2 20080901 20080923 002SQL>
SQL> SELECT phonenum,
2 MAX(decode(rn, 1, newfld, NULL)) ||
3 MAX(decode(rn, 2, ',' ||newfld, NULL)) ||
4 MAX(decode(rn, 3, ',' || newfld, NULL)) str
5 FROM (
6 SELECT phonenum,discountRule||','||begindate||','||enddate||','||usr as newfld,
7 row_number() over(PARTITION BY phonenum ORDER BY discountRule) AS rn FROM t
8 ) t
9 GROUP BY phonenum
10 ORDER BY 1;PHONENUM STR
---------- --------------------------------------------------------------------------------
7914400 a1,20080901,20080920,001,a2,20080901,20080921,002,a3,20080901,20080930,001
7915500 b1,20080901,20080912,001,b2,20080901,20080923,002
group by c1;