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 Go to next message
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 #269917 is a reply to message #269912] Tue, 25 September 2007 02:19 Go to previous messageGo to next message
Michel Cadot
Messages: 68734
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
It just have been asked a zillion times.
Search for pivot.

Regards
Michel
Re: How to convert many row data to columns? [message #270195 is a reply to message #269917] Wed, 26 September 2007 01:42 Go to previous messageGo to next message
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 #270196 is a reply to message #269917] Wed, 26 September 2007 01:47 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
Michel Cadot wrote on Tue, 25 September 2007 09:19
Search for pivot.

Regards
Michel


Did you do that? Repeating yourself won't help.

MHE
Re: How to convert many row data to columns? [message #270231 is a reply to message #270195] Wed, 26 September 2007 03:57 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #270239 is a reply to message #270238] Wed, 26 September 2007 04:36 Go to previous messageGo to next message
Michel Cadot
Messages: 68734
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Welcome to the forum.
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.

You don't need PL/SQL to do that, you can do it in SQL... as exercise.

Regards
Michel
Re: How to convert many row data to columns? [message #270278 is a reply to message #270195] Wed, 26 September 2007 06:49 Go to previous messageGo to next message
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 #270465 is a reply to message #270278] Thu, 27 September 2007 01:05 Go to previous messageGo to next message
nadia74
Messages: 85
Registered: August 2007
Member
Hi,
Thanks Michel fro your solution, but when i tried it i had error ORA-24374.
Regards
Re: How to convert many row data to columns? [message #270470 is a reply to message #270465] Thu, 27 September 2007 01:59 Go to previous messageGo to next message
Michel Cadot
Messages: 68734
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Oracle version?
Test case?
Copy and paste the execution.

Regards
Michel
Re: How to convert many row data to columns? [message #270473 is a reply to message #270470] Thu, 27 September 2007 02:16 Go to previous messageGo to next message
nadia74
Messages: 85
Registered: August 2007
Member
Hi,
I'm working on Oracle 10g, and I've tried the same example you wrote.
the massege is :
SQL*Plus internal error state 2091, context 0:0:0
Unsafe to proceed

Regards
Re: How to convert many row data to columns? [message #270479 is a reply to message #270473] Thu, 27 September 2007 03:02 Go to previous messageGo to next message
Michel Cadot
Messages: 68734
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Version means 4 decimals. Do you think there is no difference between 10.0.1 and 10.2.0.3?
This for both client and server.

Post execution means copy and paste.

Regards
Michel
Re: How to convert many row data to columns? [message #270488 is a reply to message #270479] Thu, 27 September 2007 03:25 Go to previous messageGo to next message
nadia74
Messages: 85
Registered: August 2007
Member
Hi,
Unfortunately i couldn't get it.Would you please explaine it agian.
Regards
Re: How to convert many row data to columns? [message #270499 is a reply to message #270488] Thu, 27 September 2007 03:48 Go to previous messageGo to next message
Michel Cadot
Messages: 68734
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Explain what?
I need information to give information an internal error.
You can't give me this, I can't give you that.

I don't understand you can execute the query (and get the error) but can't give me the version!

Regards
Michel

[Updated on: Thu, 27 September 2007 03:49]

Report message to a moderator

Re: How to convert many row data to columns? [message #270522 is a reply to message #270488] Thu, 27 September 2007 05:14 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
To get the server version, do:
SELECT * FROM v$version
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 #270523 is a reply to message #270499] Thu, 27 September 2007 05:17 Go to previous messageGo to next message
nadia74
Messages: 85
Registered: August 2007
Member
Hi,
I've Oracle 10.2.0.3.
Regards
Re: How to convert many row data to columns? [message #270530 is a reply to message #270523] Thu, 27 September 2007 06:32 Go to previous messageGo to next message
Michel Cadot
Messages: 68734
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
So have I.

Regards
Michel

[Updated on: Thu, 27 September 2007 06:32]

Report message to a moderator

Re: How to convert many row data to columns? [message #305047 is a reply to message #270530] Fri, 07 March 2008 15:56 Go to previous messageGo to next message
gaurav12345
Messages: 1
Registered: March 2008
Junior Member
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
Re: How to convert many row data to columns? [message #305066 is a reply to message #305047] Sat, 08 March 2008 00:06 Go to previous messageGo to next message
Michel Cadot
Messages: 68734
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
So do it.

Regards
Michel
Re: How to convert many row data to columns? [message #305070 is a reply to message #305047] Sat, 08 March 2008 00:59 Go to previous messageGo to next message
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...
Re: How to convert many row data to columns? [message #305739 is a reply to message #269912] Tue, 11 March 2008 15:17 Go to previous message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
sometime tooting ones own horn works. If you had done as Michel suggested in the beggining, you should have found this:

http://www.orafaq.com/node/1871

Good luck, Kevin
Previous Topic: SQL Help
Next Topic: User Defined Aggregate with Multiple Inputs?
Goto Forum:
  


Current Time: Wed Feb 12 05:58:36 CST 2025