neil-560592 (7/26/2012)
...Why is it missing the first two values (USD and EUR)?
Thanks
Well I'm stumped on this one. The CASE expression is a red herring - it's irrelevant. What's significant is the ORDER BY. Here's an expression which works:
SELECT @result = @result + ',' + ISNULL(result,'NULL')
FROM (
SELECT TOP (2147483647) result = c.Currencies_ShortName
--CASE
--WHEN t.Cities_ShortName = 'NYC'THEN 'USD'
--ELSE ISNULL(c.Currencies_ShortName,'NULL')
--END
FROM #Cities t
LEFT JOIN #Currencies c ON t.Cities_Id = c.Cities_Id
ORDER BY result DESC
) d
--ORDER BY result ASC
ORDER BY result DESC
...however if you change the outer ORDER BY to ASC it fails.
FWIW folks around here tend to use FOR XML PATH(), but this is quite efficient too:
SELECT
@result =
ISNULL(MAX(CASE WHEN rn = 1 THEN result END),'') +
ISNULL(MAX(CASE WHEN rn = 2 THEN result END),'') +
ISNULL(MAX(CASE WHEN rn = 3 THEN result END),'') +
ISNULL(MAX(CASE WHEN rn = 4 THEN result END),'')
FROM (
SELECT
rn = ROW_NUMBER() OVER (ORDER BY Currencies_ShortName DESC),
result =
CASE
WHEN t.Cities_ShortName = 'NYC'THEN 'USD'
ELSE ISNULL(c.Currencies_ShortName,'NULL')
END
FROM #Cities t
LEFT JOIN #Currencies c ON t.Cities_Id = c.Cities_Id
) d
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden