不用存储过程,只用一个SQL,怎么把一列的所有的值连在一起返回
例如
select col_a from testcol_a
~~~~~~~
1
2
3
4
5现在要返回这样的值
select *** result from ***result
~~~~~~~
1,2,3,4,5
例如
select col_a from testcol_a
~~~~~~~
1
2
3
4
5现在要返回这样的值
select *** result from ***result
~~~~~~~
1,2,3,4,5
dinya2003(OK):
只有一列,怎么进行行列转换?
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
SQL> select * from test; MARK
-----
88
96
84
89SQL>
SQL> select ||','||2||','||3||','||4 result from (
2 select ,lead(,1,0)over(partition by rid order by ) 2,
3 lead(,2,0)over(partition by rid order by ) 3,
4 lead(,3,0)over(partition by rid order by ) 4
5 from ( select ,1 rid from test)) where rownum=1 ;RESULT
--------------------------------------------------------------------------------
84,88,89,96SQL>
我觉得光用一个SQL不能完全解决这个问题。
----------
1
2
3
5
7select ltrim(x, ',') from (
select x from (
select level lv, sys_connect_by_path(c1, ',') x from (select c1, rownum rn from test order by 1 desc)
connect by prior rn = rn - 1)
order by lv desc
)
where rownum = 1
/