How to convert many row data to columns?

[nadia]

Hi,

I need some help on how to do this:

I've got a data set:

Code: Select all

Deptno    Ename      
10        MILLER     
20        FORD     
10        KING 
30        JAMES
10        CLARK 
20        ADAMS

The goal is to have 1 row only per Deptno

Result set should look like:

Code: Select all

Deptno    Ename1   Ename2   Ename3 
10        MILLER   KING     CLARK
20        FORD     ADAMS
30        JAMES

Any Ideas?
Thanks

[admin]

Pl visit the following link,

http://asktom.oracle.com/pls/asktom/f?p ... 9593546543

[nadia]

Hi,
I'm sorry, there was a mistake in the question.
What i mean is that I've got a data set:

Code: Select all

Location  Deptno    Ename   
NEW YORK  10        MILLER   
NEW YORK  20        FORD       
CHICAGO   10        KING        
NEW YORK  30        JAMES       
CHICAGO   20        ADAMS   

The goal is to have 1 row only per Deptno

Result set should look like for deptno=10:

Code: Select all

Location    10       20      30
NEW YORK    MILLER   FORD    JAMES
CHICAGO     KING     ADAMS

Any Ideas?
Thanks

[amitm_66]

SELECT MAX(CASE WHEN dept = 10 THEN ename END) dept_10,
MAX(CASE WHEN dept = 20 THEN ename END) dept_20,
MAX(CASE WHEN dept = 30 THEN ename END) dept_30
from (select dept,empno,ename,row_number() over(PARTITION BY dept ORDER BY ename) rn from emp) x
group by rn