In this tutorial, I am going to show you how can we build a Comma Separated Values from the Table in SQL Server.

Introduction:

In some situation, we need to create a comma separated value from the table in SQL Server. Here I am going to show you how can I build a Comma Separated value from the table.

Example: Creating Comma Separated Values from Table in SQL Server.

Create Table:

CREATE TABLE [dbo].[tblEmployee]
(
ID INT,
Name VARCHAR(100)
)

Insert Records:

INSERT INTO [dbo].[tblEmployee]([ID],[Name]) VALUES(1,'Rishab')
INSERT INTO [dbo].[tblEmployee]([ID],[Name]) VALUES(2,'Vishal')
INSERT INTO [dbo].[tblEmployee]([ID],[Name]) VALUES(3,'Ritwik')
INSERT INTO [dbo].[tblEmployee]([ID],[Name]) VALUES(4,'Kiran')
INSERT INTO [dbo].[tblEmployee]([ID],[Name]) VALUES(5,'Akhilesh')
INSERT INTO [dbo].[tblEmployee]([ID],[Name]) VALUES(6,'Lakshya')

I am using two approaches to create a Comma the first one is considered as the best way.

 

Approach 1:

Using COALESCE.

DECLARE @NameList VARCHAR(MAX)
SELECT @NameList = COALESCE(@NameList+',' , '') + [Name]
FROM [dbo].[tblEmployee]
SELECT @NameList

Output:

Comma Separated Values

Approach 2:

DECLARE @NameList VARCHAR(MAX)
SET @NameList = ''
SELECT @NameList = @NameList + [Name] + ','
FROM [dbo].[tblEmployee]
SELECT SUBSTRING(@NameList , 1, LEN(@NameList)-1)

Output:

Comma Separated Values

View More:

Conclusion:

I hope my this post will help you. I would like to have your feedback, comments, and suggestions.

Thank You.

18 Comments