Re: How do I display rows as columns in Oracle

On 12 Oct 2004, rohit.satabhai_at_bt.com wrote:
> I have multiple records for an id and I want to show all the
> detail records as columns. Is is possible by using just one sql
> ?
>
> For example using scott's emp table:
>
> scott_at_Test9iEE> select deptno, job from emp order by 1,2;
>
> DEPTNO JOB
> ---------- ---------
> 10 CLERK
> 10 MANAGER
> 10 PRESIDENT
> 20 ANALYST
> 20 ANALYST
> 20 CLERK
> 20 CLERK
> 20 MANAGER
> 30 CLERK
> 30 MANAGER
> 30 SALESMAN
> 30 SALESMAN
> 30 SALESMAN
> 30 SALESMAN
>
> 14 rows selected.
>
>
> Now to transpose this result set you would use:
>
> scott_at_Test9iEE> SELECT deptno,
> 2 max(decode(seq, 1, job, null)) job_1, 3 max(decode(seq, 2,
> job, null)) job_2, 4 max(decode(seq, 3, job, null)) job_3, 5
> max(decode(seq, 4, job, null)) job_4 6 FROM 7 (SELECT deptno,
> job, ROW_NUMBER() OVER (PARTITION BY deptno ORDER
> BY job) seq
> 8 FROM emp)
> 9 GROUP BY deptno;
>
> DEPTNO JOB_1 JOB_2 JOB_3 JOB_4
> ---------- --------- --------- --------- ---------
> 10 CLERK MANAGER PRESIDENT
> 20 ANALYST ANALYST CLERK CLERK
> 30 CLERK MANAGER SALESMAN SALESMAN
>
>
> Now if data rows increase and the dept no varies
> frequently...how could this query be modified? Do I require a
> PL SQL procedure to do so.

Search here:

  http://asktom.oracle.com/pls/ask/f?p=4950:1:

He has answered that exact question.

-- 
Galen Boyer