SQL "Matrix" Query

[TimboA]

I want to write from a table that will display the results matrix style.

The table contains UserIDs and the Roles they have been granted, for example :-

USERID ROLE
SCOTT SALES ADMIN
SCOTT FINANCE CLERK
SCOTT CREDIT MANAGER

What I want to display is :-
USERID SALES ADMIN FINANCE CLERK CREDIT MANAGER
SCOTT Y Y Y

I know I'll probably need to use DECODE somewhere along the line, but I don't want to have to hardcode the ROLES in a DECODE statement.

Anyone know how to do this without hardcoded values ??

Thanks

 

[jee]

I think you are going to need the Decode with hardcoded ROLES. You can avoid the hardcoding by creating the query dynamically and then running it. For instance, try something like this SQL*PLUS script, which assumes your table is named USER_ROLE and it has two columns, USER_ID and ROLE:

SET  feedback OFF
SET echo OFF
SET pagesize 0
SET linesize 100
spool rolequery.SQL
SELECT 'SELECT USER_ID'
FROM dual;
SELECT ', DECODE (SUM (DECODE (ROLE, ''' || ROLE || ''',1)), NULL, NULL, ''     Y     '') ' || ROLE
FROM USER_ROLE;
SELECT 'FROM USER_ROLE GROUP BY USER_ID ORDER BY USER_ID;'
FROM dual;
spool OFF
SET feedback ON
SET pagesize 60
START rolequery

 

[MikeBui]

Here is another method without having to create another SQL script, but you still have to use DECODE. Assuming your table is defined like this:

create table user_to_roles (
   userid varchar2(20),
   role   varchar2(20) )
nologging;

insert into user_to_roles values ('SCOTT','SALES_ADMIN');
insert into user_to_roles values ('SCOTT','FINANCE_CLERK');
insert into user_to_roles values ('SCOTT','CREDIT_MANAGER');

insert into user_to_roles values ('MIKE','SALES_ADMIN');

insert into user_to_roles values ('BUI','CREDIT_MANAGER');

Then create this view:

create or replace view matrix as
select
   userid,
   decode(sum(decode(role, 'SALES_ADMIN',    1, NULL)), 1, 'Y', NULL)  SALES_ADMIN,
   decode(sum(decode(role, 'FINANCE_CLERK',  1, NULL)), 1, 'Y', NULL)  FINANCE_CLERK,
   decode(sum(decode(role, 'CREDIT_MANAGER', 1, NULL)), 1, 'Y', NULL)  CREDIT_MANAGER
from user_to_roles
group by userid;

Your result is from this view:

column SALES_ADMIN    format a15
column FINANCE_CLERK  format a15
column CREDIT_MANAGER format a15
select * from matrix order by userid;

USERID               SALES_ADMIN     FINANCE_CLERK   CREDIT_MANAGER
-------------------- --------------- --------------- ---------------
BUI                                                  Y
MIKE                 Y
SCOTT                Y               Y               Y

The method is taken from an approach shown in the book written by George Kock and Kevin Loney: Oracle8 The Complete Reference on DECODE function, section "Flipping a Table onto Its Side"