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 ;
Leave a Reply