title: Assign variable with distinct records
body: <p>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:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>This will currently list all values, when I add modify the SELECT to <code>SELECT DISTINCT @concat =COALESCE(@concat, ' ') + clcontact + ', '...</code> then it will only return the first result returned by the select.</p>
  
  <p>Is there any way to return only the distinct records when assigning to a variable in conjunction with the COALESCE?</p>
  
  <p>For the following records:</p>
  
  <pre><code>clcontact
  ---------------------
  Mr S Smith
  Mrs R Jones
  Ms L Morrison
  Mrs R Jones
  </code></pre>
  
  <p>I am wanting the following to be returned: <code>Mr S Smith, Mrs R Jones, Ms L Morrison</code>.</p>