Home » SQL & PL/SQL » SQL & PL/SQL » Concatenate row values (Oracle 10g)
Concatenate row values [message #427363] Thu, 22 October 2009 04:06 Go to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

Dear All,

I have one table call operation which has four fields

operation mark_no , pos_no
RE 1001 10
HO 1001 10
RE 1002 20
HO 1003 30

How can i write a query whill concatenate the row values of operation column which has same mark_no and pos_no.

I want output like below

RE-HO 1001 10
RE 1002 20
HO 1003 30

If both Mark_no and Pos_no has two different operations it should come concatenated otherwise as it is.Please help.
Re: Concatenate row values [message #427365 is a reply to message #427363] Thu, 22 October 2009 04:18 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Tip 1: CONCAT_ALL (user defined aggregate function, can be found here).
Tip 2: group by

MHE

[Updated on: Thu, 22 October 2009 04:18]

Report message to a moderator

Re: Concatenate row values [message #427392 is a reply to message #427363] Thu, 22 October 2009 06:25 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Upgrade to 11gR2 and use LISTAGG aggregate function.
You can also use STRAGG T. Kyte function, or SYS_CONNECT_BY_PATH and so on.

Regards
Michel
Re: Concatenate row values [message #427499 is a reply to message #427363] Thu, 22 October 2009 23:41 Go to previous messageGo to next message
lakshmis
Messages: 102
Registered: November 2008
Location: India
Senior Member
SQL> SELECT * FROM test;

OPERATION     MARK_NO     POS_NO
---------- ---------- ----------
RE               1001         10
HO               1001         10
RE               1002         20
HO               1003         30

SQL> SELECT REPLACE(wm_concat(operation),',','-') operation,mark_no,pos_no FROM test GROUP BY mark_n
o,pos_no;

OPERATION     MARK_NO     POS_NO
---------- ---------- ----------
RE-HO            1001         10
RE               1002         20
HO               1003         30

Regards,
Lakshmi
Re: Concatenate row values [message #427510 is a reply to message #427363] Fri, 23 October 2009 00:20 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Read this.

regards,
Delna
Re: Concatenate row values [message #427540 is a reply to message #427499] Fri, 23 October 2009 03:30 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
@lakshmis - wmconcat is an undocumented function - Oracle are under no obligation to keep it's functionality the same in future releases.

I'd really recommend against using undocumented features in production code.
Previous Topic: selecting rows where each value appears exactly once
Next Topic: Help needed in populating a column with the column value of another row
Goto Forum:
  


Current Time: Tue Sep 27 07:40:36 CDT 2016

Total time taken to generate the page: 0.35961 seconds