SQL Server中的Concat组[duplicate]

This question already has an answer here:

这个问题已经有了答案:

• How to use GROUP BY to concatenate strings in SQL Server? 16 answers
• 如何使用GROUP BY来连接SQL Server中的字符串?16个问题

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

我知道这在使用GROUP_CONCAT的MySQL中很容易实现，但是我需要能够在MSSQL 2005中实现

Thanks

谢谢

(Duplicate of How to use GROUP BY to concatenate strings in SQL Server?)

(如何在SQL Server中使用GROUP BY来连接字符串的副本?)

5 个解决方案

#1

For a clean and efficient solution you can create an user defined aggregate function, there is even an example that does just what you need.
You can then use it like any other aggregate function (with a standard query plan):

对于干净高效的解决方案，您可以创建一个用户定义的聚合函数，甚至还有一个示例可以满足您的需要。然后，您可以像使用任何其他聚合函数一样使用它(使用标准查询计划):

#2

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

#3

See:

看到的:

http://blog.shlomoid.com/2008/11/emulating-mysqls-groupconcat-function.html

http://blog.shlomoid.com/2008/11/emulating-mysqls-groupconcat-function.html

#4

Often asked here.

经常有人问。

The most efficient way is using the FOR XML PATH trick.

最有效的方法是使用FOR XML路径技巧。

#5

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.

我刚想到一个可能的解决办法。我不知道性能如何，但我认为这是一个解决问题的有趣方法。我测试了它在一个简单的情况下可以工作(我没有为NULLs编码)。你可以试一试，看看它是否对你有好处。

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