Q
Problem solve Get help with specific problems with your technologies, process and projects.

Select * from loc

Suppose you have a table which has only two columns, namely locid (not primary) and place. In Oracle, which query should I run to get the output as above? Firstly I don't want to use any advanced feature of Oracle 9i. Is it possible using plain SQL only?

The following query is in Oracle. Suppose you have a table which has only two columns, namely locid (not primary) and place, with the following data:
select * from loc

locid place

1   Mumbai
1   Chennai
2   Hyderaba
2   Varanasi
3   New York
3   Paris
3   London
3   Tokyo
4   Beijing
Then, I want the output as following:
column 1    column 2

1 Mumbai,Chennai
2 Hydrabad, Varansi
3 New York, Paris, London, Tokyo
4 Beijing
In Oracle, which query should I run to get the output as above? Firstly I don't want to use any advanced feature of Oracle 9i. Is it possible using plain SQL only? If that's not possible then I won't mind advanced features being used as well.

You will need to write a user-defined function with a VARRAY or DECODE for this.

Something like this might work:

create or replace function
   concat( cur sys_refcursor )
return    varchar2
is
   retval varchar2(32767);
   hold   varchar2(80);

Dig Deeper on Oracle and SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close