请问如何用一句SQL将表里某个字段用逗号连接起来传给一个字符串?

Question

可以这样：
select substr(max(sys_connect_by_path(字段名,',')),2) 别名 from 
(
  select 字段名,rn,lead(rn) over(partition by 字段名 order by rn) rn1 
  from (select 字段名,row_number() over(order by 字段名 desc) rn from 表)
)
start with rn1 is null connect by rn1 = prior rn
group by 字段名

Answer 1

--通用方法
SET ECHO OFF
SET SERVEROUTPUT ON SIZE 10000
CREATE OR REPLACE PROCEDURE GET_COLS (TABLE_NAME IN VARCHAR2) AUTHID CURRENT_USER
IS
        theOWNER VARCHAR2(30);
    theTABLE VARCHAR2(30);
        str VARCHAR2(4000):='';
BEGIN
        IF INSTR(TABLE_NAME,'.')>0 THEN
                theOWNER:=UPPER(SUBSTR(TABLE_NAME,1,INSTR(TABLE_NAME,'.')-1));
                theTABLE:=UPPER(SUBSTR(TABLE_NAME,INSTR(TABLE_NAME,'.')+1));
        ELSE
                theOWNER:=USER;
                theTABLE:=UPPER(TABLE_NAME);
        END IF;        str:='';
        FOR CUR IN(
                SELECT COLUMN_NAME FROM ALL_TAB_COLS WHERE OWNER=theOWNER AND TABLE_NAME=theTABLE ORDER BY COLUMN_ID
        )LOOP
                str:= str || ',' || CUR.COLUMN_NAME;
        END LOOP;        DBMS_OUTPUT.PUT_LINE(SUBSTR(str,2));
END GET_COLS;
/--仅Oracle9i以上可用
SELECT MAX(SUBSTR(SYS_CONNECT_BY_PATH(COLUMN_NAME, ','),2)) COL
FROM (SELECT COLUMN_ID,COLUMN_NAME FROM USER_TAB_COLUMNS WHERE TABLE_NAME='&TABLE_NAME')
START WITH COLUMN_ID=1
CONNECT BY COLUMN_ID=ROWNUM;