Create a comma separated list from column using select statement

Create a comma separated list from column using select statement

18 Mar 2024
Intermediate
157K Views
1 min read

Create a comma-separated list from the column using the select statement: An Overview

Sometimes it is necessary to construct a comma-separated list of column values, such as a list of EmailIDs for mail distribution. In SQL Server, this can be accomplished by using COALESCE, as seen below. Consider using a SQL Server Tutorial or enrolling in a SQL Server Certification Course to gain additional insights and skills.

Use of COALESCE to create a comma-separated list

Suppose we have the following data in the Employee table and we need to make a semicolon-separated list of EmailIDs to send mail, then we can use COALESCE as shown in below fig.

Here I am creating a semicolon(;) separated list. You can use a comma(,) in place of the semicolon to make the comma-separated list.

For SQL Server database mail setup and configuration and more on how to send mail from SQL Server database you can refer to this article.

Summary

In this article, I expose the use of COALESCE to make comma-separated or deli-meter-separated lists. I hope after reading this article you will know how to configure SQL Database Mail in Sql Server. I would like to have feedback from my blog readers. Please post your feedback, questions, or comments about this article.

FAQs

Q1. In SQL Server, how do I convert a column into a comma-separated list?

In SQL Server, use the STRING_AGG() function with the column you want to concatenate & the delimiter (a comma).

Q2. Can I create a comma-separated list without using STRING_AGG()?

Yes, you can do this by using the XML Path concatenation method or recursive CTEs to concatenate items with custom logic.

Q3. What happens if I wish to exclude NULL values from the comma-separated list?

To exclude NULL values, just use a conditional statement within the aggregation function, like this: STRING_AGG(column_name, ',') WHERE column_name IS NOT NULL.

Q4. What happens if I wish to exclude NULL values from the comma-separated list?

To exclude NULL values, just use a conditional statement within the aggregation function, like this: STRING_AGG(column_name, ',') WHERE column_name IS NOT NULL.

Q5. Is there a maximum amount of values I may concatenate into a comma-separated list?

The STRING_AGG() method in SQL Server has a maximum input size limit of 4 MB, therefore the number of items that can be concatenated is determined by the size of the data.

Take our Sqlserver skill challenge to evaluate yourself!

In less than 5 minutes, with our skill challenge, you can identify your knowledge gaps and strengths in a given skill.

GET FREE CHALLENGE

Share Article

Live Classes Schedule

Our learn-by-building-project method enables you to build practical/coding experience that sticks. 95% of our learners say they have confidence and remember more when they learn by building real world projects.
Full Stack .Net Certification Training May 26 SAT, SUN
Filling Fast
07:00AM to 09:00AM (IST)
Get Details

Can't find convenient schedule? Let us know

About Author
Shailendra Chauhan (Microsoft MVP, Founder & CEO at Scholarhat by DotNetTricks)

Shailendra Chauhan is the Founder and CEO at ScholarHat by DotNetTricks which is a brand when it comes to e-Learning. He provides training and consultation over an array of technologies like Cloud, .NET, Angular, React, Node, Microservices, Containers and Mobile Apps development. He has been awarded Microsoft MVP 8th time in a row (2016-2023). He has changed many lives with his writings and unique training programs. He has a number of most sought-after books to his name which has helped job aspirants in cracking tough interviews with ease.
Accept cookies & close this