如何在T-SQL中将结果连接成列？

Question

我正在处理一个查询，应该给我像这样的结果： |--姓名--|--姓氏--|--语言--|--日期--| | James | Hetfield | en,gb,fr | 2011-01-01| | Lars | Ulrich | gb,fr,ca | 2011-01-01|

但是我的选择得到了一组行，如下所示： | James | Hetfield | en | 2011-01-01| | James | Hetfield | gb | 2011-01-01| | James | Hetfield | fr | 2011-01-01| | Lars | Ulrich | gb | 2011-01-01| | Lars | Ulrich | fr | 2011-01-01| | Lars | Ulrich | ca | 2011-01-01|

你推荐哪种最佳方法将结果集转换为分组列中的逗号分隔值？我发现CROSS APPLY可以完成工作，但人们说这种方法非常消耗资源。另外，数据库中有大量的数据。

提前感谢， 敬礼，Adrian

- Darj

---

CROSS APPLY需要太长时间吗？你需要加速多少？ - mmmmmm

如果在每个组内部排序不重要，那么CLR聚合可能是最快的。如果您需要按特定顺序排序，则FOR XML是唯一可靠的方法（只要您的数据不包含某些问题字符）。 - Martin Smith

CROSS APPLY 对我来说相当困惑。我需要将查询速度提高 >60%。 - Darj

CROSS APPLY如果使用正确，不会消耗太多资源。你能展示一下代码示例吗？另外，需要知道SQL Server的版本，因为这里给出的答案适用于SQL Server 2005和2008，但不适用于SQL Server 2000。最后，你为什么要这样做呢？如果你稍后在SQL中使用这些数据，似乎这是一个不好的计划，如果是为了显示，最好在GUI/客户端应用程序中完成。 - MatBailie

为Metallica的引用加一 - Reversed Engineer

Answer 1

这是最佳的连接方法，它不会像其他XML方法一样扩展特殊字符：

--Concatenation with FOR XML & eliminating control/encoded char expansion "& < >"
set nocount on;
declare @YourTable table (RowID int, HeaderValue int, ChildValue varchar(5))
insert into @YourTable VALUES (1,1,'CCC')
insert into @YourTable VALUES (2,2,'B<&>B')
insert into @YourTable VALUES (3,2,'AAA')
insert into @YourTable VALUES (4,3,'<br>')
insert into @YourTable VALUES (5,3,'A & Z')
set nocount off
SELECT
    t1.HeaderValue
        ,STUFF(
                   (SELECT
                        ', ' + t2.ChildValue
                        FROM @YourTable t2
                        WHERE t1.HeaderValue=t2.HeaderValue
                        ORDER BY t2.ChildValue
                        FOR XML PATH(''), TYPE
                   ).value('.','varchar(max)')
                   ,1,2, ''
              ) AS ChildValues
    FROM @YourTable t1
    GROUP BY t1.HeaderValue

输出：

HeaderValue ChildValues
----------- ---------------
1           CCC
2           AAA, B<&>B
3           <br>, A & Z

(3 row(s) affected)

- KM.

---

仍然可能在某些字符上出现问题。例如 CHAR(11) 或 CHAR(27)。 - Martin Smith

@Martain，说得很好，但这些并不常见，在我的任何数据库中也从未在任何用户输入或数据中看到过。 - KM.

同意 - 实际上这种情况不太可能出现。 - Martin Smith

很好的回答，谢谢。我已经在 stackoverflow.com/a/5031297/303101 上为你点了赞。 - Reversed Engineer

Answer 2

如果您的SQL版本支持FOR XML，请使用该功能。

select n.[name],CONVERT(VARCHAR(MAX),(SELECT *
        FROM (
                SELECT  l.[lang] + ' , '  AS '*'
                FROM    tblLang l
                WHERE l.[name]=n.[name]
                ) x
        FOR XML PATH (''), TYPE
    )) AS [Language]

from tblName n 

这假设表格很简单，像这样：

tblName
-------
name VARCHAR

tblLang
-------
lang VARCHAR
name VARCHAR

它还添加了末尾的逗号, :(

- El Ronnoco

---

尝试在包含 >, <, & 或任何其他 "特殊" 字符的数据上使用这种方法，你会发现它会将它们扩展成 >、< 和 &，这可能不是预期的结果或想要的结果。请参考我的答案，了解如何使用 .value 来消除这种情况。 - KM.

@KM 很有趣。老实说，我觉得 FOR XML 相当令人困惑！但是我以前曾经与它搏斗过，为了得到我想要的答案，这也是我发帖的基础... - El Ronnoco

在tsql拥有适当的CONCAT()聚合函数之前，你所能做的并不多。 - KM.