Oracle多行資料合併一行

 
--Oracle多行資料合併一行.sql
--1.
CREATE TYPE NUMBER_LIST_T AS TABLE OF varchar2(50);     
--2.   
CREATE OR REPLACE FUNCTION CONCAT_LIST (
   lst IN number_list_t, separator varchar2
)
RETURN VARCHAR2 IS
ret varchar2(2000);
BEGIN
FOR j IN 1..lst.LAST LOOP
ret := ret || separator || lst(j);
END LOOP;

RETURN ret;
END;
 
--3.  
CREATE OR REPLACE FUNCTION getUserRoles (
   userid in varchar2
)
RETURN VARCHAR2 IS
rtl varchar2(2000);
BEGIN
  SELECT Substr(CONCAT_LIST(LST, ','),2) into rtl from (
    SELECT
    CAST(MULTISET(
    SELECT trim(f_name) AS f_name FROM
       (select u.userid as f_id,r.rolename as f_name from base_role r, base_user_roles u where r.roleid=u.roleid) e
    WHERE e.f_id=m.f_id )
    AS number_list_t) LST
    FROM
       (select u.userid as f_id,r.rolename as f_name from base_role r, base_user_roles u where r.roleid=u.roleid) m
    where m.f_id = userid
    group by m.f_id
  );
  return(rtl);
END;
--select u.*, getUserRoles(u.userid) from base_user u