SELECT x, VALUE FROM (SELECT x, RTRIM ( y || ',' || LEAD (y, 1) OVER (PARTITION BY x ORDER BY y) || ',' || LEAD (y, 2) OVER (PARTITION BY x ORDER BY y) || ',' || LEAD (y, 3) OVER (PARTITION BY x ORDER BY y) || ',', ', ' ) VALUE, ROW_NUMBER () OVER (PARTITION BY x ORDER BY y) RANK FROM a) WHERE RANK = 1
SQL > select job,ename from emp;
JOB ENAME --------- ---------- CLERK SMITH SALESMAN ALLEN SALESMAN WARD MANAGER JONES SALESMAN MARTIN MANAGER BLAKE MANAGER CLARK ANALYST SCOTT PRESIDENT KING SALESMAN TURNER CLERK ADAMS
JOB ENAME --------- ---------- CLERK JAMES ANALYST FORD CLERK MILLER
已选择14行。
SQL > create or replace function f_ename(jobno in varchar2) 2 return varchar2 is 3 4 str_return varchar2(200); 5 cursor c_ename(v_jobno varchar2) is select ename from emp where job=v_jobno; 6 begin 7 for str_temp in c_ename(jobno) loop 8 str_return:=str_return | |str_temp.ename; 9 end loop; 10 return str_return; 11 end; 12 /
函数已创建。
SQL > select f_ename(job) from ( 2 select distinct job from emp);
F_ENAME(JOB) --------------------------------------- SCOTT FORD SMITH ADAMS JAMES MILLER JONES BLAKE CLARK KING ALLEN WARD MARTIN TURNER
SQL > 加上job列: SQL > select job,f_ename(job) from ( 2 select distinct job from emp);
JOB F_ENAME(JOB) ------------------------------------------------------- ANALYST SCOTT FORD CLERK SMITH ADAMS JAMES MILLER MANAGER JONES BLAKE CLARK PRESIDENT KING SALESMAN ALLEN WARD MARTIN TURNER
FROM (SELECT x,
RTRIM ( y
|| ','
|| LEAD (y, 1) OVER (PARTITION BY x ORDER BY y)
|| ','
|| LEAD (y, 2) OVER (PARTITION BY x ORDER BY y)
|| ','
|| LEAD (y, 3) OVER (PARTITION BY x ORDER BY y)
|| ',',
', '
) VALUE,
ROW_NUMBER () OVER (PARTITION BY x ORDER BY y) RANK
FROM a)
WHERE RANK = 1
JOB ENAME
--------- ----------
CLERK SMITH
SALESMAN ALLEN
SALESMAN WARD
MANAGER JONES
SALESMAN MARTIN
MANAGER BLAKE
MANAGER CLARK
ANALYST SCOTT
PRESIDENT KING
SALESMAN TURNER
CLERK ADAMS
JOB ENAME
--------- ----------
CLERK JAMES
ANALYST FORD
CLERK MILLER
已选择14行。
SQL > create or replace function f_ename(jobno in varchar2)
2 return varchar2 is
3
4 str_return varchar2(200);
5 cursor c_ename(v_jobno varchar2) is select ename from emp where job=v_jobno;
6 begin
7 for str_temp in c_ename(jobno) loop
8 str_return:=str_return | |str_temp.ename;
9 end loop;
10 return str_return;
11 end;
12 /
函数已创建。
SQL > select f_ename(job) from (
2 select distinct job from emp);
F_ENAME(JOB)
---------------------------------------
SCOTT FORD
SMITH ADAMS JAMES MILLER
JONES BLAKE CLARK
KING
ALLEN WARD MARTIN TURNER
SQL >
加上job列:
SQL > select job,f_ename(job) from (
2 select distinct job from emp);
JOB F_ENAME(JOB)
-------------------------------------------------------
ANALYST SCOTT FORD
CLERK SMITH ADAMS JAMES MILLER
MANAGER JONES BLAKE CLARK
PRESIDENT KING
SALESMAN ALLEN WARD MARTIN TURNER
http://community.csdn.net/Expert/topic/3318/3318655.xml?temp=.8208734
http://blog.csdn.net/andyxujie/archive/2004/08/27/86040.aspx