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:
Approach 2:
DECLARE @NameList VARCHAR(MAX) SET @NameList = '' SELECT @NameList = @NameList + [Name] + ',' FROM [dbo].[tblEmployee] SELECT SUBSTRING(@NameList , 1, LEN(@NameList)-1)
Output:
View More:
- Case Statement in SQL Server.
- Primary Key in SQL Server.
- Constraints in SQL Server.
- Foreign Key in SQL Server.
Conclusion:
I hope my this post will help you. I would like to have your feedback, comments, and suggestions.
Thank You.