Generate comma separated list from Table rows in SQL Server

Introduction: Hello Guys, “Welcome back” Today, i am here with another one new great article. In this article I explained the implementation about how to generate comma separated list from Table rows in SQL Server.

Join our Channel  on Whtsapp and Telagram for All Updates                                          

Implementation: I created a table with the named userdetails with the schema as follow. I inserted some records in the table. Here I am also giving SQL Server Database Script, you can execute this SQL Server Database Script by using copy and paste to create table with column and data.

         SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER ON

GO

 

SET ANSI_PADDING ON

GO

 

CREATE TABLE [dbo].[userdetails](

            [user_id] [int] IDENTITY(1,1) NOT NULL,

            [user_name] [varchar](100) NOT NULL,

            [user_email] [varchar](50) NOT NULL,

            [user_address] [varchar](250) NOT NULL,

 CONSTRAINT [PK_userdetails] PRIMARY KEY CLUSTERED

(

            [user_id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

 

GO

 

SET ANSI_PADDING OFF

GO

 

INSERT INTO Customers

SELECT 'Sanjeev', 'sanjeev@gmail.com', ‘#250 Sector 127, Chandigarh’

UNION ALL

SELECT 'Sanjay', 'sanjay@gmail.com', ‘#258 Sector 127, Chandigarh’

UNION ALL

SELECT 'Ruhika', 'ruhika@gmail.com', ‘#256 Sector 855, Chandigarh’

UNION ALL

SELECT 'Rajan', 'rajan@gmail.com', ‘#55 Sector 00, Chandigarh’

 

Generating comma separated list

There are many methods to generate the comma separated list from Table rows in SQL Server. Here I am explaining three different ways for comma separated list from Table rows in SQL Server

1.  STRING_AGG Function

2.  COALESCE Function

3.  FOR XML PATH Function

 

1. STRING_AGG Function

The STRING_AGG function is an aggregate function in SQL that concatenates expressions from multiple rows into a single string, separated by a specified delimiter. It is a highly useful function for transforming row-based data into a more concise, comma-separated (or other-delimiter-separated) string format Note: The separator isn't added at the end of string.

 

In the below example, the column values of names from userdetails table are concatenated using comma separated value using STRING_AGG function.

.

SELECT STRING_AGG([user_name], ',') 

FROM [userdetails]

 

2. COALESCE Function

The COALESCE function is a common function in SQL and other data manipulation contexts that handles NULL values. It evaluates a list of expressions in order and returns the first non-NULL value it encounters. If all expressions in the list are NULL, the function returns NULL. In this example, the user_name column values from userdetails table are concatenated using comma separated value using COALESCE function.

.

DECLARE @user_name VARCHAR(MAX)

SELECT @user_name = COALESCE(@user_name + ',', '') + [user_name]

FROM [userdetails]

SELECT @user_name

 

3. FOR XML PATH Function

The FOR XML PATH clause in SQL Server is a powerful tool used to retrieve query results in XML format, offering flexibility in structuring the generated XML. It is particularly useful for: 

• Generating XML documents:

It allows you to transform tabular data into XML, facilitating data exchange between different systems.

 In the below example, the user_name column values from userdetails table will be concatenated by using comma separated value using FOR XML PATH function.

SELECT STUFF

(

  (SELECT ',' + [user_name]

   FROM [userdetails]

   FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),

   1, 2, ''

)

Conclusion: In above code, I have been explained implementation about explained the implementation about how to generate comma separated list from Table rows in SQL Server. This code is very helpful for every developer. Bye bye and take care of yourself Developers. We will come back shortly with the new article.

 

Regards

Using Asp.net