How do I display rows as columns in Oracle

From: Rohit S <rohit.satabhai_at_bt.com>
Date: 12 Oct 2004 06:24:17 -0700
Message-ID: <2bbc95a4.0410120524.48c97d29@posting.google.com>

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. Received on Tue Oct 12 2004 - 08:24:17 CDT