Dynamic Oracle Pivot_In_Clause

I'm kinda stuck. I want to do a user-role-relationship pivot table and my query so far looks like this:

WITH PIVOT_DATA AS (
     SELECT *
     FROM
     (
         SELECT USERNAME, GRANTED_ROLE
         FROM DBA_USERS@DB_LINK U LEFT OUTER JOIN DBA_ROLE_PRIVS@DB_LINK R
         ON U.USERNAME = R.GRANTEE
      )
)
SELECT *
FROM PIVOT_DATA
PIVOT
(
    COUNT(GRANTED_ROLE)
    FOR GRANTED_ROLE
    IN('CONNECT') -- Just an example
)
ORDER BY USERNAME ASC;

It works really fine and does the job, but I don't want to write to write any role I want to search for in the pivot_in_clause, because we got like tons of them and I don't want to check every time if there are any changes.

So is there a way to write a SELECT in the pivot_in_clause? I tried it myself:

[...]
PIVOT
(
    COUNT(GRANTED_ROLE)
    FOR GRANTED_ROLE
    IN( SELECT ROLE FROM DBA_ROLES@DB_LINK )
)
[...]

But it always gives me an ORA-00936: "missing expression" in line 1 of the whole query and I don't know why. Can't there be a SELECT in the pivot_in_clause or am I doing it wrong?

This may or may not help you, but with PIVOT XML instead of the simple PIVOT you can do what you want.


Adding the XML keyword to the PIVOT operator allows us to convert the
generated pivot results to XML format. It also makes the PIVOT a
little more flexible, allowing us to replace the hard coded IN clause
with a subquery, or the ANY wildcard.

(source)

This will return obviously an XML, this is why it may not help you...

StackExchange Database Administrators Q#45016, answer score: 3