Home » SQL & PL/SQL » SQL & PL/SQL » Pivot Query
Pivot Query [message #125338] Fri, 24 June 2005 10:03 Go to next message
kpraoOracle
Messages: 1
Registered: June 2005
Junior Member
Hi,

I want to create 2 column rows for the following data

Dept Name
10 Rao
10 Miller
10 Pezdek
10 Green
20 Zettinger
20 Patel
20 Goggin
30 Ramirez
30 Martin
40 Xing
40 Feeley

I want the output to be -
Dept Name
10 Rao, Miller, Pezdek, Green
20 Zettinger, Patel, Goggin
30 Ramirez, Martin
40 Xing, Feeley

Please note that the number of employees per department is not the same but it wont exceed 10

I tried using the Pivot Query but couldnt come up with an easy solution.

Thanks for your help.
KP
Re: Pivot Query [message #125347 is a reply to message #125338] Fri, 24 June 2005 11:05 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Please search the board for the countless examples of how to do this.
Re: Pivot Query [message #125348 is a reply to message #125338] Fri, 24 June 2005 11:05 Go to previous message
Art Metzer
Messages: 2478
Registered: December 2002
Senior Member
Since you'll only have a maximum of ten names per department, you can do it this way:
SQL> CREATE TABLE t (
  2      dept        NUMBER
  3  ,   name        VARCHAR2(10)
  4  )
  5  /
 
Table created.
 
SQL> INSERT INTO t VALUES (10, 'Rao');
SQL> INSERT INTO t VALUES (10, 'Miller');
SQL> INSERT INTO t VALUES (10, 'Pezdek');
SQL> INSERT INTO t VALUES (10, 'Green');
SQL> INSERT INTO t VALUES (20, 'Zettinger');
SQL> INSERT INTO t VALUES (20, 'Patel');
SQL> INSERT INTO t VALUES (20, 'Goggin');
SQL> INSERT INTO t VALUES (30, 'Ramirez');
SQL> INSERT INTO t VALUES (30, 'Martin');
SQL> INSERT INTO t VALUES (40, 'Xing');
SQL> INSERT INTO t VALUES (40, 'Feeley');
SQL> INSERT INTO t VALUES (50, 'Justme');
 
SQL> SELECT   a.dept
  2  ,        MAX(DECODE(a.r, 1,a.name))
  3           || MAX(DECODE(a.r, 2,', ' || a.name))
  4           || MAX(DECODE(a.r, 3,', ' || a.name))
  5           || MAX(DECODE(a.r, 4,', ' || a.name))
  6           || MAX(DECODE(a.r, 5,', ' || a.name))
  7           || MAX(DECODE(a.r, 6,', ' || a.name))
  8           || MAX(DECODE(a.r, 7,', ' || a.name))
  9           || MAX(DECODE(a.r, 8,', ' || a.name))
 10           || MAX(DECODE(a.r, 9,', ' || a.name))
 11           || MAX(DECODE(a.r,10,', ' || a.name)) names
 12  FROM    (SELECT t.dept
 13           ,      t.name
 14           ,      ROW_NUMBER()
 15                  OVER (PARTITION BY t.dept
 16                        ORDER BY     NULL) r
 17           FROM   t) a
 18  GROUP BY a.dept
 19  ORDER BY a.dept
 20  /
 
      DEPT NAMES
---------- ----------------------------------------------------------------------------------------
        10 Rao, Miller, Pezdek, Green
        20 Zettinger, Patel, Goggin
        30 Ramirez, Martin
        40 Xing, Feeley
        50 Justme
 
SQL>

If you'd like to consider other options for pivoting your data, click here.
Previous Topic: how to use "OUT" in procedures
Next Topic: How to insert record into BLOB column
Goto Forum:
  


Current Time: Wed May 24 20:25:57 CDT 2017

Total time taken to generate the page: 0.25258 seconds