Home » SQL & PL/SQL » SQL & PL/SQL » Rows into Column (Oracle 10g)
Rows into Column [message #434930] Fri, 11 December 2009 13:37 Go to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
Hi
I need a single select query which converts all the rows into a single value .
Below is my requirement :
Create table email_tbl(emailid varchar2(30));
insert into email_tbl('1@y.com');
insert into email_tbl('2@y.com');
insert into email_tbl('3@y.com');
insert into email_tbl('4@y.com');

Now , I need a single select query which gives me the below results.

1@y.com,2@y.com,3@y.com,4@y.com


I have done the above by using a cursors in the pl/sql objects.But want to achieve this with a single sql/query.
Any help will be appreciable.

Thanks

[Updated on: Fri, 11 December 2009 14:12] by Moderator

Report message to a moderator

Re: Rows into Column [message #434932 is a reply to message #434930] Fri, 11 December 2009 13:44 Go to previous messageGo to next message
BlackSwan
Messages: 24903
Registered: January 2009
Senior Member
> I need a single select query which converts all the rows into a single value .

With a BILLION row table, you'd have a rather long "single value".
Not a reasonable requirement.

By the way, we don't do homework.

[Updated on: Fri, 11 December 2009 13:44]

Report message to a moderator

Re: Rows into Column [message #434934 is a reply to message #434930] Fri, 11 December 2009 13:50 Go to previous messageGo to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
Hi Swan,

I am storing the data in a temp table.At any given point of time I won't be having more than 100 records in that table.
Please give me some suggestions .As I said I have done that by using the Cursos but want to achieve by using a single query.

[Updated on: Fri, 11 December 2009 14:11] by Moderator

Report message to a moderator

Re: Rows into Column [message #434935 is a reply to message #434930] Fri, 11 December 2009 14:14 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is a question asked every week, so I assume you didn't search before posting and encourage you to do it now.

keyword: pivot.

By the way, code tags are for code or result array not for comments.

Regards
Michel

[Updated on: Fri, 11 December 2009 14:15]

Report message to a moderator

Re: Rows into Column [message #434936 is a reply to message #434930] Fri, 11 December 2009 14:15 Go to previous messageGo to next message
wakula
Messages: 150
Registered: February 2008
Location: Poland
Senior Member
pivot, join, analytic functions - those keywords should help.
Re: Rows into Column [message #434949 is a reply to message #434930] Fri, 11 December 2009 21:42 Go to previous messageGo to next message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
looks like a string aggregation problem to me.
Re: Rows into Column [message #464753 is a reply to message #434930] Fri, 09 July 2010 03:33 Go to previous messageGo to next message
nirmalnarayan
Messages: 261
Registered: April 2005
Location: India
Senior Member
Select
substr(max(sys_connect_by_path(emailid,',')),2) col1
from
(
select
emailid
,row_number() OVER (PARTITION BY emailid order by emailid, ROWNUM)rn
from
email_tb1
)
start with rn-1
connect by prior rn=rn-1
AND prior emailid=emailid
group by emaild
order by emailid


Re: Rows into Column [message #464754 is a reply to message #464753] Fri, 09 July 2010 03:37 Go to previous message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
261 posts and still unable to correctly format one.

By the way, what is the purpose of an "order by rownum"? Can you give me an example of a result set that is not ordered by rownum?

Regards
Michel
Previous Topic: Display Some value when there is no record in Table
Next Topic: PL-SQL help
Goto Forum:
  


Current Time: Sat Sep 24 16:21:11 CDT 2016

Total time taken to generate the page: 0.04348 seconds