Create a comma separated list from column using select statement

Q: In SQL Server how do I convert a column into a commaseparated list

divIn SQL Server use the STRING_AGG function with the column you want to concatenate amp the delimiter a commadiv

Q: Can I create a commaseparated list without using STRING_AGG

divYes you can do this by using the XML Path concatenation method or recursive CTEs to concatenate items with custom logicdiv

Q: What happens if I wish to exclude NULL values from the commaseparated list

divTo exclude NULL values just use a conditional statement within the aggregation function like this STRING_AGGcolumn_name WHERE column_name IS NOT NULLdiv

Q: What happens if I wish to exclude NULL values from the commaseparated list

divTo exclude NULL values just use a conditional statement within the aggregation function like this STRING_AGGcolumn_name WHERE column_name IS NOT NULLdiv

Q: Is there a maximum amount of values I may concatenate into a commaseparated list

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

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.

SQL Server powers 45% of enterprise database systems. Don’t get left behind—join our Free SQL Server Training Online today!

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.

The IT sector is exploding with 20% job growth in 2025, and .NET skills lead the charge. Join our .NET architecture training now and ride the wave to career success!

Frequently asked questions

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

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.

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.

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.

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.

About the author

Shailendra Chauhan

Microsoft MVP, Founder & CEO at ScholarHat

He is a renowned Speaker, Solution Architect, Mentor, and 11-time Microsoft MVP (2016–2026). With expertise in AI/ML, GenAI, System Design, Azure Cloud, .NET, Angular, React, Node.js, Microservices, DevOps, and Cross-Platform Mobile App Development, he bridges traditional frameworks with next-gen innovations.

He has trained 1 Lakh+ professionals across the globe, authored 45+ bestselling eBooks and 1000+ technical articles, and mentored 20+ free courses. As a corporate trainer for leading MNCs like IBM, Cognizant, and Dell, Shailendra continues to deliver world-class learning experiences through technology & AI.

View profile

.NET & C# Programming Course

.NET Microservices Course with Project

.NET System Design & Design Patterns Course with Project

ASP.NET Core & Web API Course with Project

Machine Learning with ML.NET & C# Course

Learn at Your Own Pace

.NET AI/ML Architect Bootcamp

Full-Stack .NET AI/ML Developer Bootcamp

Multi-Cloud .NET AI & Agentic AI Architect Bootcamp

Build Your Tech Career