Concatenating distinct column values in SQL Server

Question

I am trying to concatenate many columns and separating it with a comma as below:

Column
------
abc
bcd
bgd
abc

Expected output: abc,bcd,bgd

I am using this code:

CREATE FUNCTION concatinate(@PK uniqueidentifier)
RETURNS varchar(max)
AS 
BEGIN
    DECLARE @result varchar(max)

    SELECT @result = ISNULL(@result + ', ', '') + Column
    FROM table

The result I am getting is

abc,bcd,bgd,abc

I am not able to only select the distinct values. Please guide.

Answer 1

Suppose your table name is tb then your sql query would be:

SELECT dist.Column  + ','
FROM(
     SELECT DISTINCT t.Column
     FROM dbo.tb t) dist
FOR XML PATH ('')

Using this approach you will get unique values. But at the end you will get an extra comma ','. This can removed by using string helper functions. Hope this helps

Answer 2

A few amendments to your original code:

DECLARE @result varchar(max)

SELECT @result = ISNULL(@result + ', ', '') + dist.Column
FROM (SELECT DISTINCT Column FROM table) dist

PRINT(@result)

This will concatenate a distinct list. Unlike the initial answer above, you will not get an extra comma ',' at the end.