September 28, 2012 at 10:18 am
Because @MyStatusList is never NULL, the versions with ISNULL and COALESCE do not work.
But a CASE statement does work.
SELECT @MyStatusList = ISNULL(@MyStatusList,'')
+ case when len(@myStatusList) = 0 then '' else ',' end
+ StatusDesc
FROM (SELECT DISTINCT StatusDesc FROM MyStatus) x
September 28, 2012 at 10:54 am
Ninja's_RGR'us (3/21/2011)
-snip-And just for fun, a recursive CTE solution! :hehe:
DECLARE @t varchar(max);
SET @t = '';
WITH rectags AS
(
SELECT TOP 1 Tag FROM Tags ORDER BY Tag
UNION ALL
(
SELECT ', ' + Tag FROM Tags
EXCEPT
SELECT TOP 1 ', ' + Tag FROM Tags ORDER BY Tag
)
)
SELECT @t += Tag FROM rectags
SELECT @t
Could someone give an explanation for this "recursive" cte? I don't see how it's recursive or how it works (and it does work).
September 28, 2012 at 1:10 pm
You can avoid the XML characters problem in the For XML Path version by using the value() method on the XML.
IF OBJECT_ID(N'tempdb..#T') IS NOT NULL
DROP TABLE #T;
SELECT *
INTO #T
FROM ( VALUES ( '1>'), ( '2&'), ( '3/') ) AS TCV (Col);
SELECT STUFF((SELECT ',' + Col
FROM #T
ORDER BY Col DESC
FOR XML PATH(''),
TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '');
You can use either varchar or nvarchar in the value() method. Either works. I usually use nvarchar because the company I work for is multinational and has to deal with extended character set data.
The Concat/IsNull method doesn't work with Order By, is prone to string truncation, breaks if the query goes parallel (multiple threads), and is subject to change without notice if MS does work on the query engine. The XML version has none of those weaknesses. If you're using a version of SQL Server that doesn't support For XML (pre-SQL 2000), then use a cursor, not the Concat method, unless you want the code to sometimes work and sometimes not.
Note: My sample table is built using an SQL 2008+ feature, Table Value Constructor. If you want to test the final query in SQL 2005 or SQL 2000, build the sample data using Union All instead.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 28, 2012 at 1:58 pm
I have used this same FOR XML technique for various delimited string manipulation in TSQL. It works well and it does have documented quite a bit of "How to" available, google search or just MSDN help sheds light on how to do it.
Carl Demelo
President
Professional Database Services
Office: (480) 331-1302
September 28, 2012 at 3:48 pm
There's been a lot of discussion in this (oddly) revived thread today. I want to attempt to clarify something regarding a few comments in this thread, for my own benefit at least:
Hugo Kornelis (9/28/2012)
the method presented in this article is not the better and faster way, because it is undocumented, unsupported, and known to potentially return incorrect results. I must say that I am really disappointed to see this article being rerun, as that suggests that SQLServerCentral.com supports this method.
Mauve (9/28/2012)
Using the [improper] technique of SELECTing a column and performing an operation on it such as building a comma delimited string will yield unpredictable[/i] results!
According to Microsoft's KB article and Connect comments: the unpredictable, unsupported behavior occurs "when you apply any operators or expressions to the ORDER BY clause of aggregate concatenation queries". I understand that (or at least I think I do). But this has nothing to do with building a comma delimited string. The original article said nothing about applying operators to an ORDER BY clause.
So, my question: is it true that the method presented in this article is "known to potentially return incorrect results"? This is an important distinction to me - I have used the trick described in the original article a few times in production code. In fact, I think we're all guilty of "SELECTing a column and performing an operation on it such as building a comma delimited string" from time to time (ha ha).
I don't want to add fuel to the debate over which approach is most efficient. I'm simply asking about the reliability and predictability of the return values.
September 28, 2012 at 5:05 pm
BowlOfCereal (9/28/2012)
There's been a lot of discussion in this (oddly) revived thread today.
The reason for the revival of the thread is that the article was prominently featured in the newsletter.
According to Microsoft's KB article and Connect comments: the unpredictable, unsupported behavior occurs "when you apply any operators or expressions to the ORDER BY clause of aggregate concatenation queries". I understand that (or at least I think I do). But this has nothing to do with building a comma delimited string. The original article said nothing about applying operators to an ORDER BY clause.
So, my question: is it true that the method presented in this article is "known to potentially return incorrect results"?
From the relevant Microsoft Knowledge Base article: "The correct behavior for an aggregate concatenation query is undefined."
If the correct behaviour is undefined, then it's impopssible to tell if a given result is incorrect. Ergo, the method presented in this article can by definition never return "incorrect" results.
However, the results returned may be completely different than what you expect, or want. For instance, when the optimizer chooses to use a parallel plan, it could just return the result from one of the threads, which you would probably consider incorrect. I don't think that the optimizer will at this time choose a parallel plan for queries of this type (I just spent a half hour trying very hard to get it to, but failed) - but since this is undocumented behaviour of the optimizer, that might change. And if a future change to the optimizer causes it to create a parallel plan for this query, your bug reports will probably be closed as "by design" - since "the correct behavior for an aggregate concatenation query is undefined."
For me, this is enough reason to avoid this method. The XML method is a perfect replacement - and this method IS documented, and hence guaranteed.
September 28, 2012 at 5:46 pm
Hugo Kornelis (9/28/2012)
However, the results returned may be completely different than what you expect, or want. For instance, when the optimizer chooses to use a parallel plan, it could just return the result from one of the threads, which you would probably consider incorrect. I don't think that the optimizer will at this time choose a parallel plan for queries of this type (I just spent a half hour trying very hard to get it to, but failed) - but since this is undocumented behaviour of the optimizer, that might change. And if a future change to the optimizer causes it to create a parallel plan for this query, your bug reports will probably be closed as "by design" - since "the correct behavior for an aggregate concatenation query is undefined."
For me, this is enough reason to avoid this method. The XML method is a perfect replacement - and this method IS documented, and hence guaranteed.
Thanks for the reply; these are excellent points. I still read the KB article as specifically having to do with the ORDER BY issue, but I'm coming around to seeing your larger point. Today, the ORDER BY issue may be the only way to expose the problem with the "aggregate concatenation query" approach, but there's no guarantee that will be true tomorrow.
And as you point out, given that there's a perfectly sound alternative, we'd all certainly be wise to explore the XML method. That will improve my future code; whether I get time to refactor my existing stuff is another matter. :hehe:
September 29, 2012 at 6:38 am
Hello.
I'm used this one, without cursor:
declare @sep varchar(1)
declare @text varchar(max)
set @sep=''
select @text=@text + @sep + column1, @sep=',' from table
Modification for distinct version was shown: replace table by (select distinct column1 from table) as t
regards
Jarek
September 29, 2012 at 12:10 pm
I just want to say thanks, I can use this to get rid of several while loops.
September 29, 2012 at 1:57 pm
can do by for xml element . and then eliminate traling ',' with stuff.
no need of a variavle to store.direcectly can return that.
October 2, 2012 at 1:04 am
This link has got a good compilation of the possible ways to do it.
-Vinay Pugalia
If a post answers your question, please click "Mark As Answer" on that post.
Web : Inkey Solutions
Blog : My Blog
Email : Vinay Pugalia
October 6, 2012 at 9:40 am
Definitely use XML over the variable-assignment syntax, as SQL Server supports XML. The variable-assignment syntax is not documented. It works, but is not supported.
December 25, 2012 at 8:29 am
Nice article.
Viewing 13 posts - 76 through 87 (of 87 total)
You must be logged in to reply to this topic. Login to reply