SQL Server 中的连接组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/941103/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Concat groups in SQL Server
提问by Daniel Moore
If I have a table like this:
如果我有一张这样的表：
+------------+
| Id | Value |
+------------+
| 1 | 'A' |
|------------|
| 1 | 'B' |
|------------|
| 2 | 'C' |
+------------+
How can I get a resultset like this:
我怎样才能得到这样的结果集：
+------------+
| Id | Value |
+------------+
| 1 | 'AB' |
|------------|
| 2 | 'C' |
+------------+
I know this is really easy to do in MySQL using GROUP_CONCAT, but I need to be able to do it in MSSQL 2005
我知道这在 MySQL 中使用 GROUP_CONCAT 很容易做到，但我需要能够在 MSSQL 2005 中做到
Thanks
谢谢
(Duplicate of How to use GROUP BY to concatenate strings in SQL Server?)
采纳答案by Pent Ploompuu
For a clean and efficient solution you can create an user defined aggregate function, there is even an examplethat does just what you need.
You can then use it like any other aggregate function (with a standard query plan):
对于干净有效的解决方案，您可以创建一个用户定义的聚合函数，甚至还有一个示例可以满足您的需求。
然后您可以像任何其他聚合函数一样使用它（使用标准查询计划）：
回答by Aaron Alton
This will do:
这将：
SELECT mt.ID,
SUBSTRING((SELECT mt2.Value
FROM MyTable AS mt2
WHERE mt2.ID = mt.ID
ORDER BY mt2.VALUE
FOR XML PATH('')), 3, 2000) AS JoinedValue
FROM MyTable AS mt
回答by vladr
回答by Cade Roux
Often asked here.
经常在这里问。
The most efficient way is using the FOR XML PATH trick.
最有效的方法是使用 FOR XML PATH 技巧。
回答by Tom H
This just came to me as one possible solution. I have no idea as to performance, but I thought it would be an interesting way to solve the problem. I tested that it works in a simple situation (I didn't code to account for NULLs). Feel free to give it a test to see if it performs well for you.
这只是作为一种可能的解决方案出现在我面前。我不知道性能，但我认为这将是解决问题的有趣方法。我测试了它在一个简单的情况下工作（我没有编写代码来解释 NULL）。随意对其进行测试，看看它是否适合您。
The table that I used included an id (my_id). That could really be any column that is unique within the group (grp_id), so it could be a date column or whatever.
我使用的表包含一个 id (my_id)。这实际上可以是组中唯一的任何列 (grp_id)，因此它可以是日期列或其他任何列。
;WITH CTE AS (
SELECT
T1.my_id,
T1.grp_id,
CAST(T1.my_str AS VARCHAR) AS my_str
FROM
dbo.Test_Group_Concat T1
WHERE NOT EXISTS (SELECT * FROM dbo.Test_Group_Concat T2 WHERE T2.grp_id = T1.grp_id AND T2.my_id < T1.my_id)
UNION ALL
SELECT
T3.my_id,
T3.grp_id,
CAST(CTE.my_str + T3.my_str AS VARCHAR)
FROM
CTE
INNER JOIN dbo.Test_Group_Concat T3 ON
T3.grp_id = CTE.grp_id AND
T3.my_id > CTE.my_id
WHERE
NOT EXISTS (SELECT * FROM dbo.Test_Group_Concat T4 WHERE
T4.grp_id = CTE.grp_id AND
T4.my_id > CTE.my_id AND
T4.my_id < T3.my_id)
)
SELECT
CTE.grp_id,
CTE.my_str
FROM
CTE
INNER JOIN (SELECT grp_id, MAX(my_id) AS my_id FROM CTE GROUP BY grp_id) SQ ON
SQ.grp_id = CTE.grp_id AND
SQ.my_id = CTE.my_id
ORDER BY
CTE.grp_id
