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