Oracle Database Blog: Experiments & Learnings

November 11, 2011

Transpose: Power of PIVOT function in 11g

Filed under: Oracle RDBMS — Saurabh Manroy @ 1:22 pm

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.

Advertisements

1 Comment »

  1. tkyjoay

    Comment by Curtisbism — April 8, 2017 @ 5:42 am


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: