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

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.

Answer 2

ErlandSommarskog, thank you for the clarification!

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

Answer 3

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.

Answer 4

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

Answer 5

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.