 <p>If you're using 11g or later version of Oracle, the easiest way to transpose rows into columns is PIVOT operator (<a href="http://www.oracle-base.com/articles/11g/pivot-and-unpivot-operators-11gr1.php" rel="nofollow">documentation</a>). For example, consider this query - it'll give you the table of all roles and privileges with corresponding values for them:</p> <pre><code>SELECT * FROM (SELECT fj.role_name, fj.priv_name, DECODE(rp.val,1,'YES',0,'NO','-') VAL FROM (SELECT r.id role_id, r.name role_name, p.id priv_id, p.name priv_name FROM ROLE r, PRIVILEGE p) fj LEFT JOIN ROLE_PRIV rp ON fj.role_id = rp.role_id AND fj.priv_id = rp.priv_id) PIVOT (MAX(VAL) FOR PRIV_NAME IN (list_of_privileges)) ORDER BY ROLE_NAME </code></pre> <p>But there is a catch with such solution: you need to name each privilige in IN clause (or use XML keyword, but it'll mess up the result), but, considering that list of priviliges supposed to change not so frequently, it shouldn't be a problem. The following example:</p> <pre><code>WITH ROLE AS (SELECT 1 ID, 'ROLE1' NAME FROM DUAL UNION SELECT 2 ID, 'ROLE2' NAME FROM DUAL UNION SELECT 3 ID, 'ROLE3' NAME FROM DUAL), PRIVILEGE AS (SELECT 1 ID, 'READ' NAME FROM DUAL UNION SELECT 2 ID, 'WRITE' NAME FROM DUAL UNION SELECT 3 ID, 'EXECUTE' NAME FROM DUAL), ROLE_PRIV AS (SELECT 1 ROLE_ID, 1 PRIV_ID, 1 VAL FROM DUAL UNION SELECT 1 ROLE_ID, 2 PRIV_ID, 0 VAL FROM DUAL UNION SELECT 2 ROLE_ID, 1 PRIV_ID, 1 VAL FROM DUAL UNION SELECT 2 ROLE_ID, 2 PRIV_ID, 0 VAL FROM DUAL UNION SELECT 3 ROLE_ID, 1 PRIV_ID, 0 VAL FROM DUAL UNION SELECT 3 ROLE_ID, 3 PRIV_ID, 1 VAL FROM DUAL) SELECT * FROM (SELECT fj.role_name, fj.priv_name, DECODE(rp.val,1,'YES',0,'NO','-') VAL FROM (SELECT r.id role_id, r.name role_name, p.id priv_id, p.name priv_name FROM ROLE r, PRIVILEGE p) fj LEFT JOIN ROLE_PRIV rp ON fj.role_id = rp.role_id AND fj.priv_id = rp.priv_id) PIVOT (MAX(VAL) FOR PRIV_NAME IN ('READ','WRITE','EXECUTE')) ORDER BY ROLE_NAME </code></pre> <p>Will give you the following result:</p> <pre><code>ROLE_NAME 'READ' 'WRITE' 'EXECUTE' ROLE1 YES NO - ROLE2 YES NO - ROLE3 NO - YES </code></pre> <p>If you're using the earlier version of Oracle, you can consider alternative solutions (<a href="http://asktom.oracle.com/pls/apex/f?p=100:11:0%3a%3a%3a%3aP11_QUESTION_ID:31263576751669#76663048528720" rel="nofollow">like this one</a>).</p> <hr> <p>Well, if you can't use PIVOT operation, the simplest way to get the desired output would be this query:</p> <pre><code>SELECT p.name, DECODE((SELECT VAL FROM ROLE_PRIV rp WHERE rp.role_id = (SELECT ID FROM ROLE r WHERE r.name = :ROLE_NAME) AND rp.priv_id = p.id), 1, 'YES', 0, 'NO', '-') :ROLE_NAME FROM PRIVILEGE p </code></pre> <p>You can add addtional roles to this query by just adding another column. For example, take a look at this query:</p> <pre><code>WITH ROLE AS (SELECT 1 ID, 'ROLE1' NAME FROM DUAL UNION SELECT 2 ID, 'ROLE2' NAME FROM DUAL UNION SELECT 3 ID, 'ROLE3' NAME FROM DUAL), PRIVILEGE AS (SELECT 1 ID, 'READ' NAME FROM DUAL UNION SELECT 2 ID, 'WRITE' NAME FROM DUAL UNION SELECT 3 ID, 'EXECUTE' NAME FROM DUAL), ROLE_PRIV AS (SELECT 1 ROLE_ID, 1 PRIV_ID, 1 VAL FROM DUAL UNION SELECT 1 ROLE_ID, 2 PRIV_ID, 0 VAL FROM DUAL UNION SELECT 2 ROLE_ID, 1 PRIV_ID, 1 VAL FROM DUAL UNION SELECT 2 ROLE_ID, 2 PRIV_ID, 1 VAL FROM DUAL UNION SELECT 3 ROLE_ID, 1 PRIV_ID, 0 VAL FROM DUAL UNION SELECT 3 ROLE_ID, 3 PRIV_ID, 1 VAL FROM DUAL) SELECT p.name, DECODE((SELECT VAL FROM ROLE_PRIV rp WHERE rp.role_id = (SELECT ID FROM ROLE r WHERE r.name = 'ROLE1') AND rp.priv_id = p.id), 1, 'YES', 0, 'NO', '-') ROLE1, DECODE((SELECT VAL FROM ROLE_PRIV rp WHERE rp.role_id = (SELECT ID FROM ROLE r WHERE r.name = 'ROLE2') AND rp.priv_id = p.id), 1, 'YES', 0, 'NO', '-') ROLE2, DECODE((SELECT VAL FROM ROLE_PRIV rp WHERE rp.role_id = (SELECT ID FROM ROLE r WHERE r.name = 'ROLE3') AND rp.priv_id = p.id), 1, 'YES', 0, 'NO', '-') ROLE3 FROM PRIVILEGE p </code></pre> <p>It will provide you result for three roles.</p> <hr> <p>Something like this should work:</p> <pre><code>SELECT p.name, DECODE((SELECT COUNT(1) FROM ROLE_PRIV rp WHERE rp.role_id = (SELECT ID FROM ROLE r WHERE r.name = 'ROLE1') AND rp.priv_id = p.id), 0, 'NO', 'YES') ROLE1 FROM PRIVILEGE p </code></pre>