[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' SELECT COALESCE(NULL, NULL, NULL, @CoalesceTest1) AS [Test1] ,COALESCE(NULL, NULL, NULL, @CoalesceTest2) AS [Test2];
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] SELECT @List;
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;
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;
[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!