May 6, 2019 at 2:16 pm
I don't really understand the following issue. I can fix it by adding the Sorting in the subquery itself, however why does this not work?
I have a table which contains 3 rows (NL,BE,US). I want to concat those in 1 variable called @Output. When the bit @StartWithNL = 1, it should always start with NL, the rest should be order by Country ASC. If @StartWithNL = 0, then the sorting should be by Country ASC.
DECLARE
@Country1 VARCHAR(3) = 'NL',
@Country2 VARCHAR(3) = 'BE',
@Country3 VARCHAR(3) = 'US',
@StartWithNL BIT = 1;
DECLARE @Output VARCHAR(50);
SELECT @Output = ISNULL(@Output + ', ' + sub.Country, sub.Country)
FROM (
SELECT
RTRIM(@Country1) AS Country
UNION
SELECT
RTRIM(@Country2)
UNION
SELECT
RTRIM(@Country3)
) sub
WHERE LEN(sub.Country) > 0
ORDER BY CASE WHEN @StartWithNL = 1
AND sub.Country = 'NL'
THEN 0
ELSE 1 END,
sub.Country;
SELECT @Output;
May 6, 2019 at 3:25 pm
If you're really on SQL 2017, why aren't you using String_Agg()?
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
May 6, 2019 at 3:31 pm
I found this problem in multiple versions. The customer which it is made for is on an older version, however with the same syntax it also gave the strange result in SQL 2017, therefor I posted it here. Don't think it is version specific to be honest.
May 6, 2019 at 3:46 pm
I think you should be using FOR XML PATH:
DECLARE @Country1 VARCHAR(3) = 'NL',
@Country2 VARCHAR(3) = 'BE',
@Country3 VARCHAR(3) = 'US',
@StartWithNL BIT = 1;
DECLARE @Output VARCHAR(50);
SELECT @Output=STUFF((SELECT ', ' + sub.Country
FROM (SELECT RTRIM(@Country1) AS Country
UNION
SELECT RTRIM(@Country2)
UNION
SELECT RTRIM(@Country3)
) sub
WHERE LEN(sub.Country) > 0
ORDER BY CASE WHEN @StartWithNL = 1 AND sub.Country = 'NL' THEN 0
ELSE 1
END, sub.Country
FOR XML PATH('')), 1, 1,'')
SELECT @Output;
May 6, 2019 at 4:02 pm
Hi Jonathan,
That indeed is a solution I can use, however I am curious why the ORDER BY CASE does not work, and when I add the CASE in the select of the subquery, and in the ORDER BY reference to that new field it does work. Is in this case the ORDER BY evaluated before the SELECT?
For example a version which does work.
DECLARE
@Country1 VARCHAR(3) = 'NL',
@Country2 VARCHAR(3) = 'BE',
@Country3 VARCHAR(3) = 'US',
@StartWithNL BIT = 1;
DECLARE @Output VARCHAR(50);
SELECT @Output = ISNULL(@Output + ', ' + sub.Country, sub.Country)
FROM (
SELECT
RTRIM(@Country1) AS Country
,case when @Country1 = 'NL' AND @StartWithNL = 1 THEN 0 ELSE 1 END as Sorting
UNION
SELECT
RTRIM(@Country2)
,case when @Country2 = 'NL' AND @StartWithNL = 1 THEN 0 ELSE 1 END as Sorting
UNION
SELECT
RTRIM(@Country3)
,case when @Country3 = 'NL' AND @StartWithNL = 1 THEN 0 ELSE 1 END as Sorting
) sub
WHERE LEN(sub.Country) > 0
ORDER BY sub.Sorting,
sub.Country;
SELECT @Output;
May 6, 2019 at 4:06 pm
I found this problem in multiple versions. The customer which it is made for is on an older version, however with the same syntax it also gave the strange result in SQL 2017, therefor I posted it here. Don't think it is version specific to be honest.
The problem is that you are using an undocumented, unsupported "feature" and expecting it to behave in a specific manner. My reason for suggesting String_Agg() is that it is both documented and supported, but it was introduced in SQL 2017, which is why the version is important. With a prior version, I would agree that you should be using FOR XML instead, again, because it is both documented and supported.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
May 6, 2019 at 4:10 pm
It is an undocumented feature which sometimes works and sometimes doesn't. Which is why it is best to not use it.
There are a few discussion in sqlservercentral posts about when it doesn't work.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply