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:
SELECT AccountNumber, GROUP_CONCAT(DISTINCT 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.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato