Previously, I wrote a blog about how to create a comma separated list in T-SQL. 12 years later...one moment I have to go check and see if I'm really so old that I can say I wrote a blog post 12 years ago...
Ok, I'm back. It is confirmed. I am in fact officially old. I really did write that blog 12 years ago on June 23, 2010. Wow. Anyway...
12 years later that post has received dozens of views from me trying to remember how I did that. Well, I'm here to tell you there is a better way! I didn't discover this because I wanted to improve my code or because I was scanning the release notes for SQL Server to see what new T-SQL functionality has been released in the last few versions. No, I had to find a better way because my previous method which used a variable and the COALESCE function does not work on Azure Synapse Analytics.
The new method: STRING_AGG()
Let's get started with some sample data:
CREATE TABLE dbo.State
(
StateID [int],
StateName [varchar](50)
)
INSERT INTO dbo.State
SELECT 1, 'Florida'
UNION ALL
SELECT 2, 'Tennessee'
UNION ALL
SELECT 3, 'Georgia'
UNION ALL
SELECT 4, NULL
UNION ALL
SELECT 5, 'Texas'
SELECT * FROM dbo.State
Now, a simple STRING_AGG(expression, separator) and we are good to go!
Quick and simple!
A couple quick notes in closing.
- Notice that NULL values are ignored and the separator is not added. If you want to include NULL values you'll need to wrap the column name in this example with ISNULL so it would look something like SELECT STRING_AGG(ISNULL(StateName, 'No Name Provided'), ',') FROM dbo.State
- There is an optional order by operator that can be used to order the list.
- The data type is determined by the expression. That means if the column is a string it will retain the string properties. MAX fields will result in a MAX data type. Non-max fields will result in the largest possible non-MAX value (VARCHAR(8000) or NVARCHAR(4000)). All other data types result in an NVARCHAR(4000) result.
- You aren't limited to a literal string like a comma or a pipe for the separator. That's just my most common request. CHAR(13), CHAR(9), and just about any other expression are all valid as well.
