Problem solve Get help with specific problems with your technologies, process and projects.

Crosstab functionality using a view

Here's how to create a view in order to perform a crosstab-like function on your data.

Consider creating a view in order to perform a crosstab-like function on your data. This could be used if you have a key column and an attribute column, with multiple rows for each key and a different attribute value. It can be summarized so you have one row per key listing all of the attributes per row. Here's how.

Using some sample data from role_tab_privs view in Oracle:

Role    Table  Privilege 
a1      t1     insert 
a1      t1     select 
a2      t1     delete 
a2      t2     select 
a2      t2     insert 
a2      t2     delete 

You can flip the results as follows:

Role   Table   insert   select  delete 
====   =====   ======   ======  ====== 
a1     t1      insert   select 
a2     t1                       delete 
a2     t2      insert   select  delete 

Create a view as follows:

CREATE OR replace VIEW flip_role_tab_privs 
AS SELECT ROLE, table_name "Object Name", owner 
, DECODE(SUM(decode(privilege, 'UPDATE', 1, 0)), 1, 'UPDATE') "UPDATE_" 
, DECODE(SUM(decode(privilege, 'INSERT', 1, 0)), 1, 'INSERT') "INSERT_" 
, DECODE(SUM(decode(privilege, 'SELECT', 1, 0)), 1, 'SELECT') "SELECT_" 
, DECODE(SUM(decode(privilege, 'DELETE', 1, 0)), 1, 'DELETE') "DELETE_" 
, DECODE(SUM(decode(privilege, 'EXECUTE', 1, 0)), 1, 'EXECUTE') "EXECUTE_" 
FROM ROLE_TAB_PRIVS 
WHERE COLUMN_NAME IS NULL 
GROUP BY owner, ROLE, TABLE_NAME; 

You include the key fields that you want in the view, then for each distinct value in the attribute column (in this case privilege column) you create a column in the view. Now you can select from the view to get the desired results.

For More Information

  • What do you think about this tip? E-mail the Editor at tdichiara@techtarget.com with your feedback.
  • The Best Oracle Web Links: tips, tutorials, scripts, and more.
  • Have an Oracle tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
  • Ask your technical Oracle questions--or help out your peers by answering them--in our live discussion forums.
  • Check out our Ask the Experts feature: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.

Dig Deeper on Oracle database design and architecture

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close