cs_troyk (11/9/2012)

---

opc.three (11/8/2012)

---

cs_troyk (11/8/2012)

---

opc.three (11/8/2012)

---

cs_troyk (10/29/2012)

---

Not that I advocate switching to a "toy" RDBMS, but MySQL does have the GROUP_CONCAT operator which makes this type of thing a little more elegant. It's something to be aware of in case you ever need to migrate from a MySQL db.

-TroyK

MySQL's GROUP_CONCAT() function implements a semantically different piece of functionality than what Wayne has demonstrated in the article. Notice that the query in the article is not an aggregate, i.e. no GROUP BY clause.

Take a look at the CTE from the article, and consider the effect of the "DISTINCT" keyword.

Here's the MySQL solution to the comma-separated list problem:

SELECT AccountNumber, GROUP_CONCAT(Value ORDER BY Value) AS 'CommaList'

FROM T

GROUP BY AccountNumber

ORDER BY AccountNumber;

It produces the same output as the T-SQL solution.

-TroyK

Not sure what you're getting at when you talk about DISTINCT, whether you mean in the outer query or the correlated subquery, but if the subquery you would need to provide DISTINCT when using GROUP_CONCAT() as well, if duplicate values were a concern:

DISTINCT[/b] Value ORDER BY Value) AS 'CommaList'

FROM T

GROUP BY AccountNumber

ORDER BY AccountNumber;

If the outer query then yes, I agree, an aggregate may be a better option. I am not arguing about the likeness of the results however. All I am pointing out is that there is a difference in implementation, semantical maybe, but there is a penalty for using GROUP BY when it is not needed when compared to using a correlated subquery as Wayne presents it.

If GROUP_CONCAT() existed on SQL Server the XML technique from the article would almost certainly outperform it so while it may seem like a fair comparison from a query results point of view it's really apples and oranges under the hood.

Now, from a syntax point of view, when the outer query must be distinct, I like the GROUP_CONCAT() method which is why I wrote GROUP_CONCAT() for SQL Server[/url] by leveraging the SQLCLR but it is not the best choice for all scenarios.

My point is that the solution presented in the article is the equivalent to a solution using GROUP BY, so saying that there's no GROUP BY clause is only accurate when talking about the syntax. The query could be rewritten like this, and it's the same query from the optimizer's perspective:

SELECT AccountNumber,

CommaList = STUFF((

SELECT ',' + Value

FROM #TestData

WHERE AccountNumber = t.AccountNumber

ORDER BY Value

FOR XML PATH(''),

TYPE).value('.','varchar(max)'),1,1,'')

FROM #TestData t

GROUP BY AccountNumber

ORDER BY AccountNumber;

This produces the same query plan as the CTE version, and has the same IO profile, so it is incorrect to assert some penalty for using GROUP BY. I'm not sure how you arrive at the conclusion that the XML version would outperform GROUP_CONCAT if the latter were implemented in MS's product. Is this based on your results from implementing it using CLR?

-TroyK