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

This question has 7 years as of this moment, but here is a solution:

SELECT 
    STRING_AGG(Column1, ',') AS comma_separated
FROM 
    (SELECT DISTINCT Column1 FROM table1) AS T1

This applies to SQL Server 14.x and later and should give you exactly that.

Answer 3

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.