Oracle DBA 日常维护--连接查询结果

表a 列 a1 a2 
记录 1 a 
1 b 
2 x 
2 y 
2 z 
用select能选成以下结果: 
1 ab 
2 xyz 

下面有两个例子： 

1.使用pl/sql代码实现，但要求你组合后的长度不能超出oracle varchar2长度的限制 
create or replace type strings_table is table of varchar2(20); 
/ 
create or replace function merge (pv in strings_table) return varchar2 
is 
ls varchar2(4000); 
begin 
for i in 1..pv.count loop 
ls := ls || pv(i); 
end loop; 
return ls; 
end; 
/ 
create table t (id number,name varchar2(10)); 
insert into t values(1,'Joan'); 
insert into t values(1,'Jack'); 
insert into t values(1,'Tom'); 
insert into t values(2,'Rose'); 
insert into t values(2,'Jenny'); 
column names format a80; 
select t0.id,merge(cast(multiset(select name from t where t.id = t0.id) as strings_table)) names 
from (select distinct id from t) t0; 
drop type strings_table; 
drop function merge; 
drop table t; 

 

2.纯粹用sql： 

表dept, emp 
要得到如下结果 
deptno, dname, employees 
--------------------------------- 
10, accounting, clark;king;miller 
20, research, smith;adams;ford;scott;jones 
30, sales, allen;blake;martin;james;turners 

每个dept的employee串起来作为一条记录返回 
This example uses a max of 6, and would need more cut n pasting to do more than that： 
SQL> select deptno, dname, emps 
2 from ( 
3 select d.deptno, d.dname, rtrim(e.ename ||', '|| 
4 lead(e.ename,1) over (partition by d.deptno 
5 order by e.ename) ||', '|| 
6 lead(e.ename,2) over (partition by d.deptno 
7 order by e.ename) ||', '|| 
8 lead(e.ename,3) over (partition by d.deptno 
9 order by e.ename) ||', '|| 
10 lead(e.ename,4) over (partition by d.deptno 
11 order by e.ename) ||', '|| 
12 lead(e.ename,5) over (partition by d.deptno 
13 order by e.ename),', ') emps, 
14 row_number () over (partition by d.deptno 
15 order by e.ename) x 
16 from emp e, dept d 
17 where d.deptno = e.deptno 
18 ) 
19 where x = 1 
20 / 
DEPTNO DNAME EMPS 
------- ----------- ------------------------------------------ 
10 ACCOUNTING CLARK, KING, MILLER 
20 RESEARCH ADAMS, FORD, JONES, ROONEY, SCOTT, SMITH 
30 SALES ALLEN, BLAKE, JAMES, MARTIN, TURNER, WARD 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/252936/viewspace-711502/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/252936/viewspace-711502/