Is there an implementation of STRAGG function Tom Kyte that supports SQL Server?

Question

Executing the STRAGG function returns the result as a single column value. The implementation for Oracle looks pretty versatile and can be used for different tables (and relationships). Similar behavior can be achieved for SQL Server. An Internet search seems to return only hard-coded implementations, not a generic one. Do we have any known solution for Sql server?

Answer 1

There is a good XML solution for this that is widely used. This is easiest if the rows you aggregate do not have XML invalid or XML special strings, and here is an example.

SELECT * FROM ( SELECT x AS [data()] FROM ( SELECT 'something' UNION ALL SELECT 'something else' UNION ALL SELECT 'something & something' ) y (x) FOR XML PATH('') ) z (final) 

This example is from Tony Rogerson's post at http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/07/06/871.aspx

You can do much more than this simple example shows. You can specify the order of elements in aggregates (place ORDER BY in the view), you can group and join so that you get more than one row of the result, you can change the delimiters, and so on. Here are a couple of other links about this technique:

http://blogs.technet.com/wardpond/archive/2008/03/15/database-programming-the-string-concatenation-xml-trick-revisited-or-adam-is-right-but-we-can- fix-it.aspx

http://sqlblog.com/blogs/adam_machanic/archive/2009/05/31/grouped-string-concatenation-the-winner-is.aspx

Answer 2

Anita Sen did what I think the most complete answer to this question for SQL Server in his article http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact -sql / Joining row values ​​in Transact-SQL July 31, 2008. You will see that there are several different methods for this, but I believe the XML trick is the fastest. Before SQL Server 2005, we used the UPDATE trick options that I show as a comment in an article.