Is there an implementation of Tom Kyte's STRAGG function supporting SQL Server?

Question

STRAGG function implemention returns a result as a single column value. Implementation for Oracle seems pretty generic and can be consumed for different tables (and relationships). Could similar behavior be achieved for SQL Server. A search in the web, appears to return only hard coded implementations and not a generic one. Do we have any known solution for Sql server?

Answer 1

There is a nice XML solution to this that is widely used. It's simplest if the strings you're aggregating have no XML-invalid or XML-special strings, and here's 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 ordering of the items within the aggregates (put ORDER BY in the derived table), you can group and join so you get more than one result row, you can change 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

Anith Sen did what I think is the most comprehensive 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/ Concatenating Row Values in Transact-SQL 31 July 2008. You'll see that there are a number of different techniques for doing this but I reckon the XML trick is the fastest. Before SQL Server 2005, we used variations of the UPDATE trick that I show as a comment in the article.