Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers

up vote 3 down vote accepted

If you want to implement some sort of dynamic list of columns for a PIVOT, then you can look at using a procedure that generates dynamic SQL. The basic syntax of the procedure will be:

CREATE OR REPLACE procedure dynamic_pivot(p_cursor in out sys_refcursor)
as
    sql_query varchar2(1000) := 'select USERNAME ';

    begin
        for x in (select distinct ROLE from DBA_ROLES@DB_LINK)
        loop
            sql_query := sql_query ||
                ' , sum(case when GRANTED_ROLE = '''||x.Role||''' then 1 else 0 end) as '||x.Role;

                dbms_output.put_line(sql_query);
        end loop;

        sql_query := sql_query || ' FROM DBA_USERS@DB_LINK U 
                                    LEFT OUTER JOIN DBA_ROLE_PRIVS@DB_LINK R
                                      ON U.USERNAME = R.GRANTEE
                                    GROUP BY USERNAME';
        dbms_output.put_line(sql_query);

        open p_cursor for sql_query;
    end;
/

Then you can test the result using:

variable x refcursor
exec dynamic_pivot(:x)
print x;

This will gives a result similar to this:

+----------+-----+-------+-------+
| Username | DBA | Owner | Admin |
+----------+-----+-------+-------+
| Bob      |   0 |     0 |     1 |
| Jane     |   1 |     1 |     1 |
| Jim      |   1 |     1 |     0 |
+----------+-----+-------+-------+
share|improve this answer
add comment

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...

share|improve this answer
    
Thanks for your answer, but I forgot to mention that I already tried PIVOT XML but for some reason, I don't know, it cuts of my output after something like 80 characters. –  Chris.V Jun 21 '13 at 8:58
    
That must be some client strangness. Try to fiddle around with its preferences. –  dezso Jun 21 '13 at 9:04
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.