comma separated list of roles for each user
comma separated list of roles for each user. Note that this will only work on Oracle databases.
select
ROLEUSER as USER_ID,
substr(SYS_CONNECT_BY_PATH(ROLENAME, ','),2) as ROLES
from
(
select
ROLEUSER,
ROLENAME,
count(*) OVER ( partition by ROLEUSER ) as ROLECOUNT,
ROW_NUMBER () OVER ( partition by ROLEUSER order by ROLENAME) ROLESEQUENCE
from
PSROLEUSER
where
ROLEUSER is not null)
where
ROLECOUNT=ROLESEQUENCE
start with
ROLESEQUENCE = 1
connect by prior
ROLESEQUENCE+1=ROLESEQUENCE
and prior
ROLEUSER=ROLEUSER
;
