opc.three (11/8/2012)

---

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.

cs_troyk (11/8/2012)

---

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

opc.three (11/8/2012)

---

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:

[code="sql"SELECT AccountNumber, GROUP_CONCAT(DISTINCT Value ORDER BY Value) AS 'CommaList'
FROM T
GROUP BY AccountNumber
ORDER BY AccountNumber;[/code]

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 by leveraging the SQLCLR but it is not the best choice for all scenarios.

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;

cs_troyk (11/9/2012)

---

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

SELECT AccountNumber,
   dbo.GROUP_CONCAT(Value) AS CommaList
FROM #TestData t
GROUP BY AccountNumber
ORDER BY AccountNumber;

glock 71629 (1/12/2011)

---

This is exactly the situation where I use PowerShell frequently. I am able to export data from whatever complex query. Idea is something like this:

Invoke-Sqlcmd -ServerInstance myServer -Database MyDb -Query "SELECT * FROM SomeTable" | Export-Csv ./result.csv -NoTypeInformation

In -Query can be any T-SQL code and results are exported natively to CSV file. I work as ConfigMgr admin and this is the way how you can really easily receive your data.

David

...when this is so easily done without it and it works in a stored procedure or a function without having to somehow get to the command line to do it?

opc.three (5/4/2013)

---

How would you do it in T-SQL?