Home » SQL & PL/SQL » SQL & PL/SQL » How to convert many row data to columns?
How to convert many row data to columns? [message #269912] |
Tue, 25 September 2007 02:13  |
nadia74
Messages: 85 Registered: August 2007
|
Member |
|
|
Hi,
I need some help on how to do this:
I've got a data set:
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:
Deptno Ename1 Ename2 Ename3
10 MILLER KING CLARK
20 FORD ADAMS
30 JAMES
Any Ideas?
Thanks
|
|
|
|
Re: How to convert many row data to columns? [message #270195 is a reply to message #269917] |
Wed, 26 September 2007 01:42   |
nadia74
Messages: 85 Registered: August 2007
|
Member |
|
|
Hi,
I'm sorry, there was a mistake in the question.
What i mean is that I've got a data set:
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:
Location 10 20 30
NEW YORK MILLER FORD JAMES
CHICAGO KING ADAMS
Any Ideas?
Thanks
|
|
|
|
Re: How to convert many row data to columns? [message #270231 is a reply to message #270195] |
Wed, 26 September 2007 03:57   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
nadia74 wrote on Wed, 26 September 2007 08:42 |
The goal is to have 1 row only per Deptno
Result set should look like for deptno=10:
Location 10 20 30
NEW YORK MILLER FORD JAMES
CHICAGO KING ADAMS
Any Ideas?
Thanks
|
Make sure you understand your own requirements. You don't want 1 row only per Deptno, according to the required resultset
Also, the displayed resultset is not "for deptno=10"
[Updated on: Wed, 26 September 2007 03:57] Report message to a moderator
|
|
|
Re: How to convert many row data to columns? [message #270238 is a reply to message #269912] |
Wed, 26 September 2007 04:31   |
arulnithi
Messages: 1 Registered: September 2007
|
Junior Member |
|
|
hi,
This Arulnithi from India.I have found one answer but i know this is not a exact one. Here I have taken your table as kk. But
I really i don't know to bring in separate column.
select distinct deptno,kk1(10) from dual
function kk1 (dept in number) return char is
cursor c1 is select deptno,name from kk where deptno=dept;
mc1 c1%rowtype;
ss varchar2(100);
begin
open c1;
loop
fetch c1 into mc1;
exit when c1%notfound;
ss:=ss||','||mc1.name;
end loop;
close c1;
return ss;
end;
===================================
|
|
|
|
Re: How to convert many row data to columns? [message #270278 is a reply to message #270195] |
Wed, 26 September 2007 06:49   |
 |
Michel Cadot
Messages: 68734 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
With standard EMP and DEPT tables this is:
SQL> col ename format a38
SQL> col nop noprint
SQL> set head off
SQL> with
2 emp2 as (
3 select deptno,
4 max(decode(rn,1,ename))||
5 max(decode(rn,2,','||ename))||
6 max(decode(rn,3,','||ename))||
7 max(decode(rn,4,','||ename))||
8 max(decode(rn,5,','||ename))||
9 max(decode(rn,6,','||ename)) ename
10 from (select deptno, ename,
11 row_number () over (partition by deptno order by ename) rn
12 from emp)
13 group by deptno
14 ),
15 data as (
16 select d.loc, d.deptno||' '||d.dname dname, e.ename,
17 row_number () over (order by d.deptno) rn
18 from emp2 e, dept d
19 where d.deptno = e.deptno
20 ),
21 data2 as (
22 select loc, max(decode(rn,1,dname)) dname1, max(decode(rn,1,ename)) ename1,
23 max(decode(rn,2,dname)) dname2, max(decode(rn,3,dname)) dname3,
24 max(decode(rn,2,ename)) ename2, max(decode(rn,3,ename)) ename3
25 from data
26 group by loc
27 )
28 select 1 nop, 'Location', max(dname1) ename, max(dname2) ename, max(dname3) ename
29 from data2
30 union all
31 select 2 nop, loc, ename1, ename2, ename3
32 from data2
33 order by 1, 2
34 /
Location 10 ACCOUNTING 20 RESEARCH 30 SALES
CHICAGO ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
DALLAS ADAMS,FORD,JONES,SCOTT,SMITH
NEW YORK CLARK,KING,MILLER
4 rows selected.
Regards
Michel
|
|
|
|
|
|
|
|
|
Re: How to convert many row data to columns? [message #270522 is a reply to message #270488] |
Thu, 27 September 2007 05:14   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
To get the server version, do: and post the results back.
To get your SQL*Plus version, just cup and paste the text that apprears at the top when you start it up. Eg:SQL*Plus: Release 9.2.0.1.0 - Production on Thu Sep 27 11:13:34 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL>
|
|
|
|
|
|
|
Re: How to convert many row data to columns? [message #305070 is a reply to message #305047] |
Sat, 08 March 2008 00:59   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
gaurav12345 wrote on Fri, 07 March 2008 22:56 | Michel Cadot,
Do you think that you are Larry Ellison?
Please watch your tone in answering, no one is kissing your bottom to answer. You wanna answer then do, else wade off.
--Gaurav
|
Don't tell me that you registered, just to be able to post that in answer to a 6 month old thread...
|
|
|
|
Goto Forum:
Current Time: Wed Feb 12 05:58:36 CST 2025
|