Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 VIEW with COALESCE

Author  Topic 

andreacaffy
Starting Member

6 Posts

Posted - 2013-03-12 : 10:56:44
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?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-12 : 12:48:19
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
   

- Advertisement -