--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
