Help with Query - rows to columns

[athomas008]

Here is the table I have -

Resource ID | USR ID | USR GRP
-------------------------------
resource123 | 901235 | Owner
resource123 | 945678 | Manager
resource123 | 967823 | Delegate
resource123 | 932851 | Delegate
resource123 | 867324 | Delegate
resource456 | 997836 | Manager
resource456 | 776689 | Delegate

Here is how I want to query the above table and pass the result via ref cursor

Resource ID | Manager | Owner | Delegate1 | Delegate2 | Delegate3 |
-------------------------------------------------------------------
resource123 | 945678 | 901235 | 967823 | 932851 | 867324 | ..
resource456 | 997836 | | 776689 | | | ..

Can I do this in a select query? I tried using the DECODE function but it kept giving me the same delegate .

SELECT resource_id,
MAX(DECODE(usr_grp, 'MANAGER', usr_id)) AS manager,
MAX(DECODE(usr_grp, 'OWNER', usr_id)) AS owner,
MAX(DECODE(usr_grp, 'DELEGATE', usr_id)) AS delegate1,
MAX(DECODE(usr_grp, 'DELEGATE', usr_id)) AS delegate2,
MAX(DECODE(usr_grp, 'DELEGATE', usr_id)) AS delegate3,
MAX(DECODE(usr_grp, 'DELEGATE', usr_id)) AS delegate4
FROM resource_table
GROUP BY resource_id
ORDER BY resource_id;

Thank you for your help!

 

[Marco Kalter]

If you are using Oracle11g or later then perhaps you can use the PIVOT function:
http://www.oracle.com/technetwork/articles/sql/11g-pivot-097235.html

 

[athomas008]

Can it done without a pivot function.

 

[Marco Kalter]

If the columns are fixed then you can do this with subselects for each column. For example:

select deptno,
       (select sum(sal)
          from emp e2
         where e2.deptno = e1.deptno
           and job = 'MANAGER') as manager,
       (select sum(sal)
          from emp e2
         where e2.deptno = e1.deptno
           and job = 'ANALYST') as analyst,
       (select sum(sal)
          from emp e2
         where e2.deptno = e1.deptno
           and job = 'SALESMAN') as salesman,
       (select sum(sal)
          from emp e2
         where e2.deptno = e1.deptno
           and job = 'CLERK') as clerk
  from emp e1
 group by deptno
 order by deptno