SQL Server-将行连接到以逗号分隔的列表中

本文介绍了SQL Server-将行连接到以逗号分隔的列表中的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

假设我有一个临时表，如下所示:

Let's suppose I have a temporary table which looks like this:

+----+------+
| Id | Value|
+----+------+
| 1  |   1  |
| 1  |   2  |
| 1  |   3  |
| 2  |   1  |
| 2  |   2  |
+----+------+

我希望我的桌子像这样:

And I want my table to be like this:

    +----+----------+
    | Id | ValueList|
    +----+----------+
    | 1  |   1,2,3  |
    | 2  |   1,2    |
    +----+----------+

因此，基本上，我需要将我的值作为逗号分隔的列表进行分组.我已经尝试过以下方法:

So basically I need to group my values as a comma separated list.I already tried the following:

SELECT Id, STUFF((SELECT ',' + CAST(VALUE AS varchar) FROM @MyTable FOR XML PATH('')), 1 ,1, '') AS ValueList
FROM @MyTable
GROUP BY Id

但是我得到类似的东西

        +----+---------------------+
        | Id |      ValueList      |
        +----+---------------------+
        | 1  |   1,1,1,1,1,1,...   |
        +----+---------------------+

我找不到我做错了什么.有人可以帮忙这个查询吗?或为我指明正确的方向?谢谢.

I cant find what I am doing wrong. Could someone help with this query? Or point me to a right direction?Thank you.

推荐答案

您缺少子查询中的条件.

You are missing the condition inside the sub query.

SELECT t2.Id, STUFF((SELECT ',' + CAST(VALUE AS varchar) FROM @MyTable t1  where t1.Id =t2.ID FOR XML PATH('')), 1 ,1, '') AS ValueList
FROM @MyTable t2
GROUP BY t2.Id

演示

这篇关于SQL Server-将行连接到以逗号分隔的列表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！