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.

Read More Denormalise listagg children on single row comma separated
February 24, 2014

Oracle remove duplicates parent child with listagg

De-duplicating parent – child table sets of data here using the listagg function to merge all the child rows into a single denormalised column. set serverout on BEGIN FOR d1 IN ( WITH denorm AS ( SELECT parent.aos_code , parent.pre_group_id , LISTAGG ( child.aos_code || child.stud_pass ) WITHIN GROUP ( ORDER BY child.aos_code || child.stud_pass […]

Read More Oracle remove duplicates parent child with listagg
February 10, 2014