SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 VIEW with COALESCE
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

andreacaffy
Starting Member

6 Posts

Posted - 03/12/2013 :  10:56:44  Show Profile  Reply with Quote
I have a table with two fields:
ID / SURNAME

for the same ID is possible that there are many SURNAME.

I would like to have something like this:

if the table have these records:
1 -- SUR1
1 -- SUR2
1 -- SUR3
2 -- SUR4
2 -- SUR5
3 -- SUR6

I would like to have.
1 -- SUR1/SUR2/SUR3
2 -- SUR4/SUR5
3 -- SUR6

I have 7500 records. If I use a function with COALESCE is very slow.

This is my function :
FUNCTION [dbo].[AllSurnames]
(
	@Id int
)
RETURNS varchar(200)
AS
BEGIN
	DECLARE @Names VARCHAR(8000)  
	SELECT @Names = COALESCE(@Names + '/ ', '') + Surname FROM T_PEOPLE WHERE (r_id_family = @Id) GROUP BY Surname

return @names

END


And then I use a view:
SELECT  ID, dbo.AllSurnames(ID) AS Surname
FROM dbo.T_FAMILY


If I exec view I wait for 15 seconds.
Any idea in order to develop and optimize this?

I read that cursor is a good solution, but how can I build a cursor?

Edited by - andreacaffy on 03/12/2013 10:57:44

James K
Flowing Fount of Yak Knowledge

3761 Posts

Posted - 03/12/2013 :  12:48:19  Show Profile  Reply with Quote
Cursor is probably a bad solution - in all likelihood, it will be slower. Don't use a function for it - do it in-line, like shown below:
select
	a.id,
	STUFF(b.AllSurnames,1,1,'') as AllSurnames
from
	(select distinct ID from dbo.T_FAMILY) as a
	cross apply
	( select '/'+surname from dbo.T_PEOPLE b
	where b.id = a.id
	for xml path('')
	) b(AllSurnames);
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000