Pivoting using Coalesce

Question

Hello!

Yesterday I got the following tip from mssqltips.com: The Many Uses of Coalesce in SQL Server and was puzzled a lot: I still don't understand what "feature" of Coalesce allows it to pivot data.

According to the MS's documentation coalesce just returns the first non-null value and that is all, but when used with the variable - coalesce (variable, '') + ... it may be used to pivot data:

Here are the two examples showing that adding "coalesce" to the code changes nothing in the output...

...so why "coalesce (variable).. makes that variable to contain not only the last value (Executive) as in the second example but all of them instead???

SELECT @DepartmentName = COALESCE(@DepartmentName,'') + Name + ';'
FROM HumanResources.Department
WHERE (GroupName = 'Executive General and Administration')
SELECT @DepartmentName AS DepartmentNames
go

Thank you in advance,
Michael

Answer 1

what "feature" of Coalesce allows it to pivot data.

Has nothing directly to do with the COALESCE and you don't PIVOT data.
The query you have is a "quirky update"; just search for

Answer 2

By the way, the connection between building a string aggregate and a dynamic pivot, is that a preparing step of a dynamic pivot is form a string with all the column values that will be the names of the columns in the dynamic pivot.

Answer 3

Hi @Mikhail Firsov ,

Adding to what other experts have said.

I provide a simple example as below:

create table #temp  
(Groupid int,  
Name varchar(10))  
  
insert into #temp values  
(1,'AA'),  
(1,'BB'),  
(1,NULL),  
(1,'CC')  
  
select * from #temp  

As you could see, there is one NULL in the Name column.

We would not get the expected output using below statement.

DECLARE @Name VARCHAR(1000)   
  
SELECT @Name = COALESCE(@Name,'') + Name + ';'    
FROM #temp  
WHERE Groupid=1  
  
SELECT @Name AS Names   

Output:

Names  
CC;  

In this condition, we also need to add COALESCE to name column.

DECLARE @Name VARCHAR(1000)   
  
SELECT @Name = COALESCE(@Name,'') + COALESCE(Name,'') + ';'    
FROM #temp  
WHERE Groupid=1  
  
SELECT @Name AS Names   

Output:

Names  
AA;BB;;CC;  

We could also use STRING_AGG instead even if there is NULL in the Name column.

DECLARE @Name VARCHAR(1000)   
  
SELECT @Name = STRING_AGG(Name ,';')    
FROM #temp  
WHERE Groupid=1  
  
SELECT @Name AS Names   

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

Good day,

I hope this can help to clarify, so here are my 2 cents

The Many Uses of Coalesce in SQL Server

This post provide only a singe uses of COALESCE. It uses COALESCE in order to replace the value when is null and since it does not SET the value at the start then the COALESCE function replaces the first value into empty string ''

Using Coalesce to Pivot

As Olaf Helper said, This blog's post has nothing to do with PIVOT.

It provides a solution to chain (concate/combine) values that are in a column in the table and add a separator between the values.

Yes, string_agg NOW does the same thing as the command you posted.

This is not correct

First and the most important in this case is that the function string_agg do not add the separator when your value is NULL while the approach of using the COALESCE function simply replace the NULL with '' and adds the separator on NULL values

DROP TABLE IF EXISTS #temp  
create table #temp(Groupid int,Name varchar(10))  
insert into #temp values  
(1,'AA'),  
(1,'BB'),  
(1,NULL),  
(1,'CC')  
select * from #temp  
GO  

-- Using COALESCE  
DECLARE @Name VARCHAR(1000)   
SELECT @Name = COALESCE(@Name,'') + COALESCE(Name,'') + ';'    
FROM #temp  
SELECT @Name AS Names   
GO -- AA;BB;;CC;  

-- Using STRING_AGG  
DECLARE @Name VARCHAR(1000)  
SELECT @Name = STRING_AGG([Name],';')  
FROM #temp  
SELECT @Name AS Names   
GO --AA;BB;CC;  

Second different which is also VERY important but was not mention in the discussion yet is that the approach of using SELECT @X = @X + Column does not guarantees the order of the result and you might get different results while using the exact same database and query!

In general, I HIGHLY recommend NOT to use this approach

Using STRING_AGG we can configure the order using WITHIN GROUP ( ORDER BY <order_by_expression_list> [ ASC | DESC ] )

In this condition, we also need to add COALESCE to name column.

As I explained above, if there are values which are NULL then using COALESCE is not the same as STRING_AGG and the solution in old versions of SQL Server should be to use CASE statement if you want to use the approach ofSELECT @X = @X + Column`

DECLARE @Name VARCHAR(1000)   
SET @Name = ''  
SELECT @Name = @Name + CASE WHEN [Name] IS NULL THEN '' ELSE [Name] + ';'  END  
FROM #temp  
SELECT @Name AS Names   
GO --AA;BB;CC;  

But again I do not like this approach and I will probably use FOR XML in this case for this task.

Point to think about: if you have only a single value and you want to replace it when it is NULL then using COALESCE might work, but I would prefer to use the function which was built for this ISNULL

Instead of using COALESCE to configure the starting value to '' I would prefer to use SET

So instead of using :

DECLARE @Name VARCHAR(1000)   
SELECT @Name = COALESCE(@Name,'') + COALESCE(Name,'') + ';'    
FROM #temp  
SELECT @Name AS Names   
GO -- AA;BB;;CC;  

I would prefer probably using SET and ISNULL:

DECLARE @Name VARCHAR(1000)   
SET @Name = ''  
SELECT @Name = @Name + ISNULL(Name,'') + ';'    
FROM #temp  
SELECT @Name AS Names   
GO --AA;BB;;CC;  

VERY IMPORTANT TO REMEMBER that using SET like in the last sample is NOT the same as using COALESCE if we have values which are NULL.

Here is example and basic illustration of steps to explain the differences

DECLARE @Name VARCHAR(1000)  
SELECT @Name = COALESCE(@Name,'') + Name + ';'    
FROM #temp  
SELECT @Name AS Names   
GO -- CC;  
--Why?  
-- Step 1: before we start the value is null so the server use '' and add AA = AA  
-- Step 2: the server use 'AA' and add BB = AA;BB;  
-- Step 3: the server use 'AA;BB;' and add NULL = NULL  
-- Step 4: the value is null so the server use '' and add CC = CC  


DECLARE @Name VARCHAR(1000)  
SET @Name = ''  
SELECT @Name = @Name + Name + ';'    
FROM #temp  
SELECT @Name AS Names   
GO -- NULL   
-- Why?  
-- Using this approach the different is in step 4 since now we do not replace the NULL with '' and we have NULL + CC = NULL  

---

Hope this was useful :-)

Have great day guys

💬Ronen Ariely

Personal Site | Blog | Facebook | Linkedin