Geek asks and answers

Assign a variable with separate entries

I created a Table-Valued function to return a comma separated list, but I have problems in the list to contain only individual values. My function is this:

CREATE FUNCTION [dbo].[clientContactsConcat] ( @clnum INT )
RETURNS NVARCHAR(4000)
AS BEGIN
    DECLARE @concat NVARCHAR(4000)
    SELECT @concat =COALESCE(@concat, ' ') + clcontact + ', '
    FROM    dbo.client
    WHERE crelated = @clnum
    AND NOT clcontact IS NULL
    SELECT  @concat = SUBSTRING(@concat, 2, LEN(@concat) - 2)
    RETURN ( @concat )
END

This list lists all the values, when I add change SELECT to SELECT DISTINCT @concat =COALESCE(@concat, ' ') + clcontact + ', '..., then it will return only the first result returned by select.

Is there a way to return only individual records when assigning a variable along with COALESCE?

For the following entries:

clcontact
---------------------
Mr S Smith
Mrs R Jones
Ms L Morrison
Mrs R Jones

I need to return the following: Mr S Smith, Mrs R Jones, Ms L Morrison.

+3
source share
1 answer

You can try putting SELECT DISTINCTin a view.

 SELECT @concat =COALESCE(@concat, ' ') +  clcontact
 FROM
 (

    SELECT DISTINCT  clcontact + ', ' AS clcontact
    FROM    dbo.client
    WHERE crelated = @clnum
    AND NOT clcontact IS NULL
  ) T

, , , SQL Server 2000.

+2

More articles:


All Articles