select * from loc locid place 1 Mumbai 1 Chennai 2 Hyderaba 2 Varanasi 3 New York 3 Paris 3 London 3 Tokyo 4 BeijingThen, I want the output as following:
column 1 column 2 1 Mumbai,Chennai 2 Hydrabad, Varansi 3 New York, Paris, London, Tokyo 4 BeijingIn 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
Related Q&A from Don Burleson
I have created a table, Party, and in it there are two main fields, party_code and party_name, and around 2,500 records are available in it. I passed... Continue Reading
I have a query that retrieves around 10 records. I have a task to concatenate the values separated with ','. I would like to know if Oracle has any ... Continue Reading
I have a barcode application running on a Sun platform with Oracle9i. Oracle is 92% full. I have space in other locations. Is there any way to ... Continue Reading