Using Coalesce to Clean Up Reports

Question

Using Coalesce to Clean Up Reports

Emmitt Albright

Old Hand

Points: 385
More actions
August 16, 2021 at 3:56 pm

#3916914

Comments posted to this topic are about the item Using Coalesce to Clean Up Reports

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

gbritton1 SSCertifiable Points: 6522 More actions · Answer 1

I seem to recall that this sort of thing is sometimes discouraged:

SELECT  @catlist = @catlist + COALESCE(p.ProductCategory1, p.ProductCategory2, 'NA')  + ', '
 FROM dbo.Product AS p

You can use FOR XML PATH('') or STRING_AGG (since SQL Server 2017):

SELECT @catlist = STRING_AGG(COALESCE(p.ProductCategory1, p.ProductCategory2, 'NA'), ', ')
 FROM dbo.Product as p

michel.droguettc Newbie Points: 1 More actions · Answer 2

I think this is a error typo is this query: , oh.,oh.OrderDate

SELECT CustomerID,

COALESCE(oh.OrderCompleteDate, oh.OrderProcessDate, oh.OrderUpdatedDate, oh.OrderReceivedDate, oh.,oh.OrderDate) AS 'OrderDate'

FROM dbo.OrderHeader AS oh;

Emmitt Albright Old Hand Points: 385 More actions · Answer 3

michel.droguettc wrote:

I think this is a error typo is this query: , oh.,oh.OrderDate
SELECT CustomerID, COALESCE(oh.OrderCompleteDate, oh.OrderProcessDate, oh.OrderUpdatedDate, oh.OrderReceivedDate, oh.,oh.OrderDate) AS 'OrderDate' FROM dbo.OrderHeader AS oh;

thanks, I've corrected this and submitted a new version

Emmitt Albright Old Hand Points: 385 More actions · Answer 4

gbritton1 wrote:

I seem to recall that this sort of thing is sometimes discouraged:

I have seen a few people recommend using for xml, but not a blog that shows why or the exact differences. I've used it many times without issue. If you have a reference, I'd like to read it.

gbritton1 SSCertifiable Points: 6522 More actions · Answer 5

good discussion here: https://stackoverflow.com/questions/15138593/nvarchar-concatenation-index-nvarcharmax-inexplicable-behavior

relevant section to this application:

Even without ORDER BY, we do not guarantee that @var = @var + will produce the concatenated value for any statement that affects multiple rows. The right-hand side of the expression can be evaluated either once or multiple times during query execution and the behavior as I said is plan dependent.

I do not know how to find the old connect articles, which is a pity. Still, today, there is no reason to use the form

select @var = @var +...

given the alternatives

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 6

gbritton1 wrote:

good discussion here: https://stackoverflow.com/questions/15138593/nvarchar-concatenation-index-nvarcharmax-inexplicable-behavior
relevant section to this application:
Even without ORDER BY, we do not guarantee that @var = @var + will produce the concatenated value for any statement that affects multiple rows. The right-hand side of the expression can be evaluated either once or multiple times during query execution and the behavior as I said is plan dependent.
I do not know how to find the old connect articles, which is a pity. Still, today, there is no reason to use the form
select @var = @var +...
given the alternatives

Even with an ORDER BY - the order is not guaranteed. It can and will be different depending on whether or not you include ASC/DESC or blank. I think this change was introduced in 2016 - but was mostly seen when using the form of SELECT @var = column which returned 'incorrect' values.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Zidar SSC Enthusiast Points: 126 More actions · Answer 7

Not directly about coalesce, but since we are also talking about ways to concatenate rows, worth checking is a relatively recent addition to T-SQL functions is string_agg (expression, separator) . Example:

SELECT Object_ID, [name], [Parent_object_id]
FROM [sys].[check_constraints]
; returns someting like this
Object_ID   name                                         Parent_object_id
----------- -------------------------------------------- ----------------
859150106   CK_SubmittedTestAttempts_no_future_Start     1109578991
1038626743  Stage_Number_NOT_NULL                        1810105489
1054626800  Stages_test_design_version_id_NOT_NULL       1810105489

(3 rows affected)

We need values from column [Name] in a list:

SELECT string_agg([name], ', ') AS MyLista
FROM [sys].[check_constraints]
; returns
MyLista
--------------------------------------------------------------------------------------------------------
CK_SubmittedTestAttempts_no_future_Start, Stage_Number_NOT_NULL, Stages_test_design_version_id_NOT_NULL

SELECT string_agg([name], ' ---- ') WITHIN GROUP (ORDER BY [name] DESC) AS MyLista
FROM [sys].[check_constraints]
; returns
MyLista
----------------------------------------------------------------------------------------------------------------
Stages_test_design_version_id_NOT_NULL ---- Stage_Number_NOT_NULL ---- CK_SubmittedTestAttempts_no_future_Start

Function string_agg allows sorting and many other nice things. The only limitation - output string is limited to nvarchar(4000). Beyond that, it may work, or not. That's why God gave us FOR XML

I could possibly write an article with examples. So many nice things are possible, some of them we implemented in my shop.

🙂

Zidar's Theorem: The best code is no code at all...