SQL Tip: Create comma separated string from values in a column (& COALESCE)

[edit: April 2019] This was originally posted to my internal (to Microsoft) blog on March 19, 2012 as part of a series I called ‘SQL Tips’ for coworkers and an email distribution list people could sign up to. I have left this as originally written but have updated formatting for my WordPress theme. Note: there are different ways to achieve this, at the time of writing this tip I didn’t want to show all the ways but rather show one way.

Not a very catchy title I know, but hopefully something useful nonetheless. There are times when I need to create one record from a query that returns multiple records in a column. This is actually quite simple to do but will require the use of a variable and the built-in function COALESCE.

The COALESCE function:

This function will return the first non-NULL value in the list of items given. It’s kind of like the ISNULL function on crack. Unlike the ISNULL function you can pass it many columns or values and it will look for the first item that IS NOT NULL and return that. If all values or columns are NULL then it will return NULL.

Test Coalesce query:

DECLARE  @CoalesceTest1 varchar(10) = NULL
        ,@CoalesceTest2 varchar(10) = 'blah'
       ,COALESCE(NULL, NULL, NULL, @CoalesceTest2) AS [Test2];

The output:

Test1 Test2
NULL blah

Creating the one record value:

Now that we understand the COALESCE function, let’s see how to create our one record value. The basic structure f the query is:

DECLARE @List nvarchar(max);
SELECT @List = COALESCE(@List + N'[What you want to separate the values with]', N'') + [Column Name]
  FROM [table name]

Thus, if I wanted to create a comma separated list of all the column names in a particular table or view (I have and do use this when writing some of my scripts actually) I would use something like:

DECLARE @Columns nvarchar(max);
SELECT  @Columns = COALESCE(@Columns + N', ', N'') + name
  FROM sys.all_columns
 WHERE object_id = OBJECT_ID(N'SysResList')
 ORDER BY column_id;
SELECT @Columns;

The result:

SysResList_PK, NALPath, SiteCode, RoleName, ResourceType, ServerName, ServerRemoteName, PublicDNSName, InternetEnabled, Shared, Certificate, SslState, ForestFQDN, DomainFQDN, IISPreferredPort, IISSslPreferredPort, IsAvailable, CertThumbprint, Flags

If I want to have the column names (or whatever value) surrounded with single quotes or brackets or anything at all I could add that quite easily:

DECLARE @Columns nvarchar(max);
SELECT  @Columns = COALESCE(@Columns + N’, ‘, N”) + N'[‘+name+N’]’
  FROM sys.all_columns
 WHERE object_id = OBJECT_ID(N’SysResList’)
 ORDER BY column_id;
SELECT @Columns;

The result: 

[SysResList_PK], [NALPath], [SiteCode], [RoleName], [ResourceType], [ServerName], [ServerRemoteName], [PublicDNSName], [InternetEnabled], [Shared], [Certificate], [SslState], [ForestFQDN], [DomainFQDN], [IISPreferredPort], [IISSslPreferredPort], [IsAvailable], [CertThumbprint], [Flags]

Can you find a good (or interesting) use for this tip? Please share with the rest of us if you do!


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s