举例,现有一个5行2列的表:
1 a
1 b
1 c
2 d
2 e
能不能用一条select语句查出这样的结果:
1 abc
2 de盼高手答复,困扰好久了...
1 a
1 b
1 c
2 d
2 e
能不能用一条select语句查出这样的结果:
1 abc
2 de盼高手答复,困扰好久了...
解决方案 »
- 关于ORACLE和SQLSERVER的交互
- oracle 移植问题
- 如何查看事务的提交状态?
- Oracel与C#接连问题
- 请问哪里有Oracle report 9i 下载?
- 往oracle9i数据库插入字符串也出问题,大家帮看看,在线等!
- 安装oracle9后,通过VB ADO 不能连接DB了,请高手帮忙
- 如何取消隐藏列??
- 高手快帮帮我这个菜鸟吧
- 请问要想在每天22:00 执行一个叫prc_test的过程,要怎样写这个job?~
- 急!三天了!oracle10g服务能正常启动,可是SQLPLUS连接不了,“TNS:监听进程不能解析在连接描述符中给出的SERVICE_NAME”
- Oracle Olap api的开发问题(急)
如
create table tb
(
id number,
name varchar2(100)
);
insert into tb values(1,'aa');
insert into tb values(2,'bb');
insert into tb values(3,'cc');
insert into tb values(1,'dd');
insert into tb values(1,'ee'); 实现
1 aa,dd,ee
2 bb
3 cc
--------------------------create or replace function t_f
(
v_id number
)
return varchar2
is
result varchar2(5000);
begin
result := '';
for x in (select name from tb where id = v_id)
loop
result := result || x.name || ',';
end loop; result := substr(result, 1, length(result) - 1);
return result;
end t_f;
select id, t_f(id) as name from (select distinct id from tb) x
----------------下面是例子
Oracle9i中使用SYS_CONNECT_BY_PATH进行行列转换- -
有表:
SQL> SELECT deptno, ename FROM emp ORDER BY deptno, ename; DEPTNO ENAME
------ ----------
10 CLARK
10 KING
10 MILLER
20 ADAMS
20 FORD
20 JONES
20 SCOTT
20 SMITH
30 ALLEN
30 BLAKE
30 JAMES
30 MARTIN
30 TURNER
30 WARD 14 rows selected.
想输出为:
DEPTNO ENAME
------ ----------
10 CLARK, KING, MILLER
20 ADAMS, FORD, JONES, SCOTT, SMITH
30 ALLEN, BLAKE, JAMES, MARTIN, TURNER, WARD 除了使用聚集函数或者存储过程之外,9i中可以:
SQL> SELECT deptno
2 , LTRIM(MAX(SYS_CONNECT_BY_PATH(ename,','))
3 KEEP (DENSE_RANK LAST ORDER BY curr),',') AS concatenated
4 FROM ( SELECT deptno
5 , ename
6 , ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) AS curr
7 , ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) -1 AS prev
8 FROM emp )
9 GROUP BY deptno
10 CONNECT BY prev = PRIOR curr AND deptno = PRIOR deptno
11 START WITH curr = 1;
DEPTNO----------CONCATENATED----------------------------------------------------------------------------------------------------
10CLARK,KING,MILLER
20ADAMS,FORD,JONES,SCOTT,SMITH
30ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD10CLARK,KING,MILLER 20ADAMS,FORD,JONES,SCOTT,SMITH 30ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
肯定收集了好多帖子收藏的和我收藏的是一样的
select id, ltrim(max(sys_connect_by_path(name,',')) keep(dense_rank last or
der by curr),',') as concatenated from (select id, name, row_number() over (part
ition by id order by name) as curr, row_number() over (partition by id order by
name) -1 as prev from t1)
group by id
connect by prev=prior curr and id = prior id
start with curr=1;
2 UNION
3 SELECT 1 ID,'B' NAME FROM DUAL
4 UNION
5 SELECT 1 ID,'C' NAME FROM DUAL
6 UNION
7 SELECT 2 ID,'D' NAME FROM DUAL
8 UNION
9 SELECT 2 ID,'E' NAME FROM DUAL
10 )
11 select ID,WMSYS.WM_CONCAT(NAME) NAME
12 FROM A
13 GROUP BY ID
SQL> /
ID NAME
---------- --------------------------------------------------------------------------------
1 A,B,C
2 D,E
(select 1 col1,'a' col2 from dual
union all
select 1,'b' from dual
union all
select 1,'c' from dual
union all
select 2,'d' from dual
union all
select 2,'e' from dual
)
select col1,replace(wm_concat(col2),',','') from tb
group by col1