VIEW with COALESCE - SQL Server Forums

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);

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources