table:t 其表内容如下col1 col2
--------------
001 vl1
001 vl2
001 vl3如何用sql语句实现如下结果:col1 col2
------------------
001 vl1,vl2,vl3谢谢?
USER ID_SESSION SCN HOST LOCAL NOW
---------- --------------- ---------- --------------- --------------- -----------------
TEST 23,5746 561194174 172.16.1.66 172.16.1.13 20051216 16:11:48SQL> create table t(col1 varchar2(10),col2 varchar2(10));表已创建。SQL>
SQL> insert into t values('001','vl1');已创建 1 行。SQL> insert into t values('001','vl2');已创建 1 行。SQL> insert into t values('001','vl3');已创建 1 行。SQL> insert into t values('002','vl1');已创建 1 行。SQL> insert into t values('002','vl2');已创建 1 行。SQL> insert into t values('002','vl3');已创建 1 行。SQL> insert into t values('002','vl4');已创建 1 行。SQL> COMMIT;提交完成。SQL> select * from t;COL1 COL2
---------- ----------
001 vl1
001 vl2
001 vl3
002 vl1
002 vl2
002 vl3
002 vl4已选择7行。SQL> COL COL2 FORMAT A20SQL> SELECT COL1,LTRIM(MAX(SYS_CONNECT_BY_PATH(COL2,',')),',') COL2
2 FROM
3 (
4 SELECT COL1,COL2,MIN(COL2) OVER(PARTITION BY COL1) COL2_MIN,
5 (ROW_NUMBER() OVER(ORDER BY COL1,COL2))+(DENSE_RANK() OVER (ORDER BY COL1)) NUMID
6 FROM T
7 )
8 START WITH COL2=COL2_MIN CONNECT BY NUMID-1=PRIOR NUMID
9 GROUP BY COL1;COL1 COL2
---------- --------------------
001 vl1,vl2,vl3
002 vl1,vl2,vl3,vl4SQL> drop table t;表已丢弃。SQL> exit
--------------
001 vl1
001 vl2
001 vl3如何用sql语句实现如下结果:col1 col2
------------------
001 vl1,vl2,vl3谢谢?
USER ID_SESSION SCN HOST LOCAL NOW
---------- --------------- ---------- --------------- --------------- -----------------
TEST 23,5746 561194174 172.16.1.66 172.16.1.13 20051216 16:11:48SQL> create table t(col1 varchar2(10),col2 varchar2(10));表已创建。SQL>
SQL> insert into t values('001','vl1');已创建 1 行。SQL> insert into t values('001','vl2');已创建 1 行。SQL> insert into t values('001','vl3');已创建 1 行。SQL> insert into t values('002','vl1');已创建 1 行。SQL> insert into t values('002','vl2');已创建 1 行。SQL> insert into t values('002','vl3');已创建 1 行。SQL> insert into t values('002','vl4');已创建 1 行。SQL> COMMIT;提交完成。SQL> select * from t;COL1 COL2
---------- ----------
001 vl1
001 vl2
001 vl3
002 vl1
002 vl2
002 vl3
002 vl4已选择7行。SQL> COL COL2 FORMAT A20SQL> SELECT COL1,LTRIM(MAX(SYS_CONNECT_BY_PATH(COL2,',')),',') COL2
2 FROM
3 (
4 SELECT COL1,COL2,MIN(COL2) OVER(PARTITION BY COL1) COL2_MIN,
5 (ROW_NUMBER() OVER(ORDER BY COL1,COL2))+(DENSE_RANK() OVER (ORDER BY COL1)) NUMID
6 FROM T
7 )
8 START WITH COL2=COL2_MIN CONNECT BY NUMID-1=PRIOR NUMID
9 GROUP BY COL1;COL1 COL2
---------- --------------------
001 vl1,vl2,vl3
002 vl1,vl2,vl3,vl4SQL> drop table t;表已丢弃。SQL> exit
sum(decode(dnnum,'北京',dnnum,0)),sum(decode(dnnum,'南京',dnnum,0))
from table1 group by dddate;
from (
SELECT dddate,dnnum,LEVEL l,replace(sys_connect_by_path(dnnum,';'),';') a FROM (SELECT dddate||ROWNUM c,dddate||ROWNUM-1 p,dddate, dnnum FROM table1)
CONNECT BY PRIOR c = p )
2006年1月8日
“较复杂的ORACLE行列转换”