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 email@example.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.