Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQL concatenating other attribute while one attribute is equal
PL/SQL concatenating other attribute while one attribute is equal [message #358129] Sat, 08 November 2008 20:29 Go to next message
floverboy
Messages: 4
Registered: November 2008
Junior Member
SSN 	        DEPNAME 	
333445555 	Administration 	
333445555 	Headquarters 	
987654321 	Headquarters 	
123456789 	NONE 	 
222222222 	NONE 	 
333333333 	NONE 	 
444444444 	NONE 	 
453453453 	NONE 	 
666884444 	NONE 	 
886655555 	NONE 	 
987987987 	NONE 	 
999887777 	NONE 	 


the above is a cursor
i am creating a procedure which needs to join depnames if ssn is equal

i want answer like

SSN 	        DEPNAME 	                 
333445555 	'Administration, Headquarters'  	
987654321 	Headquarters 	                
123456789 	NONE 	 
222222222 	NONE 	 
333333333 	NONE 	 
444444444 	NONE 	 
453453453 	NONE 	 
666884444 	NONE 	 
886655555 	NONE 	 
987987987 	NONE 	 
999887777 	NONE 	 


the depnames of ssn 333445555 administration and headquarters must be joined like 'Administration, Headquarters'

i am confused to do this.pleaase help me out



[mod-edit: code tags added, next time please add them yourself]

[Updated on: Sat, 08 November 2008 22:03] by Moderator

Report message to a moderator

Re: PL/SQL concatenating other attribute while one attribute is equal [message #358132 is a reply to message #358129] Sat, 08 November 2008 22:09 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
There are various ways that you can do this. You could use wm_concat or concat_all or Tom Kyte's stragg funciton, or just loop through an ordered cursor concatenating values. You can search this forum for various examples. Please read the forum guide for how to post a proper question, including Oracle version, create table and insert statements, and providing what you have tried so far and what results or errors you get using code tags to preserve formatting.
icon14.gif  Re: PL/SQL concatenating other attribute while one attribute is equal [message #359130 is a reply to message #358129] Thu, 13 November 2008 18:41 Go to previous messageGo to next message
floverboy
Messages: 4
Registered: November 2008
Junior Member
I used the following code with LEAD function because i am using oracle 10g

select group_key, string
from
(
select
group_key ,
row_number() over ( partition by group_key order by val ) as position ,
val ||
lead( ','||val, 1 ) over ( partition by group_key order by val ) ||
lead( ','||val, 2 ) over ( partition by group_key order by val ) ||
lead( ','||val, 3 ) over ( partition by group_key order by val ) ||
lead( ','||val, 4 ) over ( partition by group_key order by val ) ||
lead( ','||val, 5 ) over ( partition by group_key order by val )
as string
from t
)
where position = 1
order by group_key ;

[Updated on: Thu, 13 November 2008 21:13]

Report message to a moderator

Re: PL/SQL concatenating other attribute while one attribute is equal [message #359161 is a reply to message #359130] Thu, 13 November 2008 23:30 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and use code tags.
Use the "Preview Message" button to verify.

Regards
Michel
Previous Topic: can compile procedure but cannot execute it
Next Topic: ORA-01410: invalid ROWID need help!
Goto Forum:
  


Current Time: Sat Dec 10 20:51:04 CST 2016

Total time taken to generate the page: 0.12336 seconds