有支持SQL Server的Tom Kyte的STRAGG函数的实现吗？ [英] Is there an implementation of Tom Kyte's STRAGG function supporting SQL Server?
问题描述
STRAGG函数实现将结果作为单个列值返回。 Oracle的实现似乎很通用，可以用于不同的表（和关系）。类似的行为可以实现SQL Server。在网络中搜索，似乎只返回硬编码的实现，而不是一般的。我们有什么已知的解决方案为Sql server？
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?
推荐答案
有一个很好的XML解决方案，如果你聚合的字符串没有XML无效或XML特殊字符串，这是最简单的，这里是一个例子。
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)
b $ b
此示例来自Tony Rogerson在 http ：//sqlblogcasts.com/blogs/tonyrogerson/archive/2006/07/06/871.aspx
您可以做的不仅仅是这个简单的例子显示。您可以指定聚合中的项的顺序（在导出表中为put ORDER BY），您可以分组和连接，以便获得多个结果行，可以更改分隔符等。以下是有关此技术的其他几个链接：
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
这篇关于有支持SQL Server的Tom Kyte的STRAGG函数的实现吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
