Get column values as a String or list(separated by comaas or Space) in SQL Server

Introduction:

Sometime we want to show data in a column of data table as a String separated by commas or space.

 In this article I will explain how to get column values as comma separated String or list or we can say Convert/ Show the Column values of the table of the Sql server database and show as a comma separated string/row. 

E.g If the Column has the value

Arjun

Raman

Raghav

Sameer

Kapil

Result required: Arjun,Raman,Raghav,Sameer,Kapil

Or

Arjun Raman Raghav Sameer Kapil

Implementation:

Separated By Commas:

DECLARE @Str varchar(max)

SELECT @Str=COALESCE(@Str,'') + CAST(Column_Name as varchar(100)) + ','

FROM Table_Name

SELECT @Str

The above query will return the values of a particular column in a row/list separated by comma.

 Separated By Space:

DECLARE @Str varchar(max)

SELECT @Str=COALESCE(@Str,'') + CAST(Column_Name as varchar(100)) + ' '

FROM Table_Name

SELECT @Str

The above query will return the values of a particular column in a row/list separated by a single space.