Transpose: Power of PIVOT function in 11g

This post outlines basic usage of Oracle’s pivot function in sql.

Requirement was to create a list of granted roles that can be exported to Excel sheet. The list would contain One user per record and each column would list few (predefined) roles assigned to user.

First thing that came to mind was decode function, but the way I was using it: listed one record for each role that was granted to a user.  For example, the output looked like:

USERNAME                       RESOURCE        DBA
------------------------------ --------------- ----------
SCOTT                          YES             NO
SCOTT                          NO              YES

Since the requirement is to have one row per user, we needed to transpose the ‘granted_role’ column of dba_role_privs.   For this, Pivot function provided by Oracle (starting 11g)  serves the purpose.  So, using pivot function,  query was changed to something like this:

select * from (
	select username, granted_role
	from dba_role_privs r, dba_users u where
	u.username=r.grantee(+)
	and u.username not in ('SYS','SYSTEM','DBSNMP','ORACLE_OCM','CTXSYS'))
pivot
(
count(granted_role)
for granted_role in ('RESOURCE','DATAPUMP_EXP_FULL_DATABASE','EXP_FULL_DATABASE','DATAPUMP_IMP_FULL_DATABASE','DBA','IMP_FULL_DATABASE')
) order by username;

Output for SCOTT user:

USERNAME    'RESOURCE' 'DATAPUMP_EXP_FULL_DATABASE' 'EXP_FULL_DATABASE' 'DATAPUMP_IMP_FULL_DATABASE' 'DBA' 'IMP_FULL_DATABASE'
----------- ---------- ---------------------------- ------------------- ---------------------------- ----- -------------------
SCOTT                1                            0                   0                    0            1                   0

Only aggregate functions can be used as an argument to Pivot function. That is the reason count(granted_role) was performed. This works perfectly in our case because the count would be either 1 or 0 (always) depending upon if a role is granted or not.

About these ads