Members Members   Search Search   Help Help   Register Register   Login Login   Home Home
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: 276
Registered: November 2004
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
Report message to a moderator
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: 689
Registered: August 2006
Location: Stockholm/Sweden
Senior Member
What about using SYS_CONNECT_BY_PATH operator ?
Report message to a moderator
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: 17653
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
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
Report message to a moderator
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: 276
Registered: November 2004
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
Report message to a moderator
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: 205
Registered: September 2006
Location: Dehradun
Senior Member
http://www.orafaq.com/forum/m/295367/92734/#msg_295367


regards,
Report message to a moderator
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: 17653
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
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
Report message to a moderator
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: 276
Registered: November 2004
Senior Member
Hi,

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

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

[ Syndicate this forum (XML) ] [ ]

Current Time: Thu Jul 24 15:16:03 CDT 2008

Total time taken to generate the page: 0.01872 seconds
.:: Forum Home :: Site Home :: Wiki Home :: Contact :: Privacy ::.