question

MikhailFirsov-1277 avatar image
0 Votes"
MikhailFirsov-1277 asked JeffreyWilliams-3310 commented

Pivoting using Coalesce

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:
118264-01.png

Here are the two examples showing that adding "coalesce" to the code changes nothing in the output...
118207-03.png
118208-02.png

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

118292-05.png

Thank you in advance,
Michael


sql-server-transact-sql
01.png (78.5 KiB)
03.png (54.4 KiB)
02.png (92.2 KiB)
05.png (56.6 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @MikhailFirsov-1277,

I have converted Viorel's comment to answer.

Please validate all the answers so far and remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread. 

Thank you for understanding!

Best regards,
Melissa

0 Votes 0 ·
Viorel-1 avatar image
0 Votes"
Viorel-1 answered

Did you also try this?

 DECLARE @DepartmentName VARCHAR(1000) = ''
 SELECT @DepartmentName = @DepartmentName + Name + ';'
 FROM . . .

The queries seem to perform concatenation, which is called "pivot" by the author. Maybe you can ask a question in the comment section.


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

...don't know how to mark your answer as the Answer here...

0 Votes 0 ·
MikhailFirsov-1277 avatar image
0 Votes"
MikhailFirsov-1277 answered

Oh, I did try

DECLARE @DepartmentName VARCHAR(1000) = ''
SELECT @DepartmentName = @DepartmentName + Name + ';'
FROM . . .

...but got "null", so this is important: = '' ! Coalesce just adds the '' instead of being typed in the DECLARE ... string!

Thank you very much!!!

Regards,
Michael

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

Beware that the correct result of

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

is undefined. That is, you may get what you expect, or you may get something else.

To compose a string like this, use string_agg if you are on SQL 2017 or higher. For earlier version, use FOR XML PATH. While the syntax of the latter is not particularly intuitive, it is guaranteed to work.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MikhailFirsov-1277 avatar image
0 Votes"
MikhailFirsov-1277 answered TomPhillips-1744 commented

ErlandSommarskog, thank you for the clarification!

...but why the result is undefined? Doesn't string_agg do the same string concatenation as "+" ?

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Yes, string_agg NOW does the same thing as the command you posted. The @var = COALESCE(... was the way to accomplish string_agg before string_agg existed.

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered JeffreyWilliams-3310 commented

String_agg does it in a defined way. It is an aggregate function.

SELECT with variable assignment is only well defined when the SELECT statement returns a single row. The Docs says that if you say:

SELECT @x = col FROM tbl


@x will be assigned the value from the last row, but "last row" has no meaning in a relational database.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I uderstand - thank you once again for the explanation!

Regards,
Michael

0 Votes 0 ·

Even worse - add an ORDER BY without an ASC vs an ORDER BY with ASC or DESC and check the results. The results may surprise you.

0 Votes 0 ·
OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered MikhailFirsov-1277 commented

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




· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OlafHelper-2800, thank you very much - I'll read about it!

Regards,
Michael

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered SameIP commented

Hi @MikhailFirsov-1277,

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

118417-output.png

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.


output.png (1.6 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

pituach avatar image
0 Votes"
pituach answered pituach edited

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 of` SELECT @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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.