急，sql行拼接，实现了马上给分

Question

create table test1 (
  id int,
  name varchar(20)
)insert into test1 values(1, 'AAA');
insert into test1 values(2, 'BBB');
insert into test1 values(3, 'CCC');想用一条sql语句查出全部
结果为一个字符串 显示格式为  1：AAA|2:BBB|3:CCC

Answer 1

SQL> select replace(wm_concat(id||':'||name),',','|') from test1;
 
REPLACE(WM_CONCAT(ID||':'||NAM
--------------------------------------------------------------------------------
1:AAA|2:BBB|3:CCC
 
SQL> 

Answer 2

SELECT REPLACE(wm_concat(id || ':' || NAME), ',', '|') FROM test1;

Answer 3

如果没有wm_concat函数,可以使用下面的脚本构造
create or replace TYPE zh_concat_im  
AUTHID CURRENT_USER AS OBJECT
(
  CURR_STR VARCHAR2(32767),
  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT zh_concat_im) RETURN NUMBER,
  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT zh_concat_im,  
  P1 IN VARCHAR2) RETURN NUMBER,
  MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN zh_concat_im,
  RETURNVALUE OUT VARCHAR2,
  FLAGS IN NUMBER)
  RETURN NUMBER,
  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT zh_concat_im,  
  SCTX2 IN zh_concat_im) RETURN NUMBER
);
/create or replace TYPE BODY zh_concat_im
IS
  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT zh_concat_im)
  RETURN NUMBER  
  IS
  BEGIN
  SCTX := zh_concat_im(NULL) ;
  RETURN ODCICONST.SUCCESS;
  END;
  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT zh_concat_im,
  P1 IN VARCHAR2)  
  RETURN NUMBER  
  IS
  BEGIN
  IF(CURR_STR IS NOT NULL) THEN  
  CURR_STR := CURR_STR || ':' || P1;
  ELSE
  CURR_STR := P1;
  END IF;
  RETURN ODCICONST.SUCCESS;
  END;
  MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN zh_concat_im,
  RETURNVALUE OUT VARCHAR2,
  FLAGS IN NUMBER)
  RETURN NUMBER  
  IS
  BEGIN
  RETURNVALUE := CURR_STR ;
  RETURN ODCICONST.SUCCESS;
  END;
  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT zh_concat_im,  
  SCTX2 IN zh_concat_im)  
  RETURN NUMBER  
  IS
  BEGIN
  IF(SCTX2.CURR_STR IS NOT NULL) THEN  
  SELF.CURR_STR := SELF.CURR_STR || ':' || SCTX2.CURR_STR ;
  END IF;
  RETURN ODCICONST.SUCCESS;
  END;
END;
/
create or replace FUNCTION wm_concat(P1 VARCHAR2)  
RETURN VARCHAR2 AGGREGATE USING zh_concat_im ;

Answer 4

--wm_concat函数在10g中可以使用，但9i中没有些函数
--9i可以使用sys_connect_by_path来实现：
SELECT ltrim(MAX(sys_connect_by_path(id || ':' || NAME, '|')), '|') str
  FROM (SELECT row_number() over(ORDER BY id) rn, t.* FROM test1 t) a
 START WITH rn = 1
CONNECT BY PRIOR rn = rn - 1;