Sunday, December 16, 2018

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
--------------------------------------------------------------------------------------------------------

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.