CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2000user-defined-functions
Lima
Lima

Reputation: 1211

Assign variable with distinct records

I have created a Table-Valued function to return a comma seperated list, but am having problems in the list to contain only distinct values. My function is as follows:

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 will currently list all values, when I add modify the SELECT to SELECT DISTINCT @concat =COALESCE(@concat, ' ') + clcontact + ', '... then it will only return the first result returned by the select.

Is there any way to return only the distinct records when assigning to a variable in conjunction with the COALESCE?

For the following records:

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

I am wanting the following to be returned: Mr S Smith, Mrs R Jones, Ms L Morrison.

Upvotes: 3

Views: 7521

Answers (1)

Martin Smith
Martin Smith

Reputation: 453328

You could try putting the SELECT DISTINCT into a derived table.

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

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

Note that this method of concatenating strings is not guaranteed to work but I'm not sure of any obviously better way in SQL Server 2000.

Upvotes: 2

Related Questions