August 16, 2021 at 1:26 pm
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
August 16, 2021 at 1:42 pm
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;
August 16, 2021 at 3:25 pm
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
August 16, 2021 at 3:28 pm
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.
August 16, 2021 at 3:56 pm
Comments posted to this topic are about the item Using Coalesce to Clean Up Reports
August 17, 2021 at 4:12 pm
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
August 17, 2021 at 6:39 pm
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
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
August 18, 2021 at 7:53 pm
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...
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply