How to Convert Multiple Rows from a Column into a Comma Separated Values List in SQL Server

How to Convert Multiple Rows from a Column into a CSV List in SQL Server

Suppose we have the Table with data as follows..

Declare @RegionTable As Table
(
RgnName Varchar(50)
)

Insert Into @RegionTable 
Values ( 'East'),('West'),('123.456'),('South'),('987.654'),('North')

Select * From @RegionTable

GO

Now we can convert these rows into a single List of Comma Separated Values using various Methods as follows..
1) Using COALESCE Function:
The COALESCE Function evaluates the arguments in order and always returns first non-null value from the defined argument list.
Declare @RegionTable  As  Table
(
RgnName Varchar(50)
)
Insert Into @RegionTable Values ( 'East'),('West'),('123.456'),('South'),('987.654'),('North')
Select * From @RegionTable

Declare @listStr Varchar(4000)
Select @listStr = COALESCE(@listStr+',' ,'') + RgnName
From @RegionTable
Select @listStr As 'RgnList'
GO

Output:

2) Using SubString Function:

Declare @listStr Varchar(4000)
Set @listStr = ''
Select @listStr = @listStr + RgnName + ','
From [dbo].[RegionTable]
Select Substring(@listStr , 1, LEN(@listStr)-1) As 'RgnList'
GO

3) Using SubString Function with XML Method :

Select Substring((Select ',' + r.[RgnName] From @RegionTable r
                             Order By r.[RgnName]
                             FOR XML PATH('')),2,150000) AS RgnList

GO

Result:

--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------