Andrew Fraser DBA

Denormalise listagg children on single row comma separated

You can denormalise data using the listagg function, so to display users with their roles on a single line comma separated:

SELECT grantee
     , LISTAGG ( granted_role , ' , ' ) WITHIN GROUP ( ORDER BY granted_role ) AS granted_roles
  FROM dba_role_privs
 GROUP BY grantee
 ORDER BY 1 ;

H/t Adrian Billington.

Include users who don’t have any roles with an outer join:

SELECT INITCAP ( parent.username ) username
     , COUNT ( * ) roles
     , LISTAGG ( INITCAP ( child.granted_role ) , ' , ' ) WITHIN GROUP ( ORDER BY child.granted_role ) AS granted_roles
  FROM dba_users parent
  LEFT OUTER JOIN dba_role_privs child
    ON parent.username = child.grantee
 GROUP BY INITCAP ( parent.username )
 ORDER BY 1 ;