Home » SQL & PL/SQL » SQL & PL/SQL » Merging multiple rows into single row (9i)
Merging multiple rows into single row [message #319527] Mon, 12 May 2008 01:59 Go to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Hi,

In a table X I have following values

BR    CODE
--    ----
1001   1
1001   2
1001   3
1002   1
1002   2
1003   1
1003   2


I want to get o/p like this
BR     CODE
--     ----
1001   1,2,3
1002   1,2
1003   1,2


Can anybody suggest me to get this output using a single SQL statement(not using PL/SQL)..

Brayan
Re: Merging multiple rows into single row [message #319532 is a reply to message #319527] Mon, 12 May 2008 02:06 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

What about using SYS_CONNECT_BY_PATH operator ?
Re: Merging multiple rows into single row [message #319541 is a reply to message #319527] Mon, 12 May 2008 02:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Search for "pivot".
I should point this question is asked several times each week out to you and you should search BEFORE posting.

Regards
Michel
Re: Merging multiple rows into single row [message #319552 is a reply to message #319527] Mon, 12 May 2008 03:06 Go to previous messageGo to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Hi Michel,

I did search for PIVOT, but the problem here is I can not use aggregate function for group, becase I need to concatenate he CODE's with a comma separator .

Instead I've done using below query.
SELECT BR,
       concatenate_list(CURSOR(SELECT x.CODE FROM src_tel_no x WHERE x.BR = y.BR)) phone_nos
FROM   (SELECT DISTINCT BR FROM src_tel_no ) y;		


But here it required to create a function. I wanted without any PL/SQL's

Regards,
Brayan
Re: Merging multiple rows into single row [message #319557 is a reply to message #319527] Mon, 12 May 2008 03:39 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
http://www.orafaq.com/forum/m/295367/92734/#msg_295367


regards,
Re: Merging multiple rows into single row [message #319574 is a reply to message #319552] Mon, 12 May 2008 04:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have a look at SnippetyJoe's site: http://www.sqlsnippets.com
and his SQL techniques pages: http://www.sqlsnippets.com/en/topic-12027.html:

Columns to Rows
Columns to String
Rows to Columns
Rows to String
String to Columns
String to Rows

Regards
Michel
Re: Merging multiple rows into single row [message #319857 is a reply to message #319527] Tue, 13 May 2008 04:11 Go to previous message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Hi,

Thanks a lot Michel and mshrkshl, it worked for me.

Brayan.
Previous Topic: what is autonomous pragma transaction in function and procedure
Next Topic: File Integrity check
Goto Forum:
  


Current Time: Sat Dec 03 22:24:38 CST 2016

Total time taken to generate the page: 8.42406 seconds