SQL Server – Get Comma Separated Values from table

I had requirement to get the Comma Separated Values from the table for specific column. So here sharing with you example how i handle this situation.

USE tempdb
GO
CREATE TABLE test1
(
column1 INT,
column2 VARCHAR(6)
)

INSERT INTO test1
VALUES
(1,'Test1'),
(1,'Test2'),
(2,'Test3'),
(2,'Test4'),
(3,'Test5')

Now see the table

SELECT * FROM TEST1

 Create a function to get the Comma Separated Values as below

CREATE FUNCTION dbo.CommaSeparatedvalues
(
@parameter1 AS INT
)
RETURNS VARCHAR(5000)
AS
BEGIN

DECLARE @CSV VARCHAR(MAX)

SELECT @CSV = COALESCE(@CSV + ', ', '') + column2
FROM test1
WHERE column1 = @parameter1

RETURN @CSV

END

-- Result 

SELECT dbo.CommaSeparatedvalues (1)

If anybody have any other idea please share as comment.

Related Post:
http://varindersandhu.in/2011/05/17/sql-server-import-csv-file-to-sql-table-bulk-insert/