Concatenating distinct column values in SQL Server

sql-server sql-server-2008 sql-function
Gerald Baretto picture Gerald Baretto · Aug 29, 2015 · Viewed 7.2k times · Source

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

Yasin picture Yasin · Aug 29, 2015

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 ('')

By this u will get unique values. But at the end you will extra comma ',' you can remove it by string functions. Hope this works

Related questions

pass parameter in table valued function using select statement

I have created a table valued return function which returns me a table . here is call of my function as follow SELECT * FROM dbo.[StateFixedTaxesCalculation](3020,16,1,1006) and its working OK for me , now i want to use this function call in …

Read More
How to call a recursive function in sql server

I have a table as follows cat_id Cat_Name Main_Cat_Id 1 veg null 2 main course 1 3 starter 1 4 Indian 2 5 mexican 2 6 tahi 3 7 chinese 3 8 nonveg null 9 main course 8 10 indian 9 11 starter 8 12 tahi 11 13 chinese 11 (Main_Cat_Id is cat_id of previously added category …

Read More
Incompatible object type when create and alter a table value function in SQL

I'm getting the below error for the given function. Msg 2010, Level 16, State 1, Procedure GetTableFromDelimitedValues, Line 2 Cannot perform alter on 'dbo.GetTableFromDelimitedValues' because it is an incompatible object type. IF NOT EXISTS(SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(…

Read More