没有聚合函数和行分组的分组

Hi guys I have a table that has 2 columns

大家好，我有一个有两列的表

LoadID      coment
1234        comment1
1234        comment2
1234        comment3
12345       comment4
28366356    comment5
28366356    comment6
1212        comment7

I am trying to run this code to get the comments for a specific LoadID to group together which should look like this (comment1,comment2,comment3)

我正在尝试运行这段代码，以获得一个特定LoadID的注释，以便将其分组在一起(comment1,comment2,comment3)

SELECT coment + ',' AS 'data()'
FROM TB_SOT_Comment
Where LoadID = '1234'
FOR XML PATH('')

The code is working fine but the problem is that I want to change the where clause to do the same for all the loadID's. The catch is that the loadID's are not constants and they change all the time based on the operator input so I can't really specify the number I just need to find a way to write a code that groups all the similar loadid's in the table and connect the comments of that loadID together so basically I want the output to look like this

代码运行良好，但问题是我想要更改where子句，以便对所有loadID执行相同的操作。问题是loadID的不是常量和他们所有的时间变化基于操作员输入所以我不能指定数量我只需要找到一个方法来编写一个代码组表中所有类似loadID的和连接的评论loadID在一起所以我希望输出看起来像这样

LoadID      coment
1234        comment1,comment2,comment3
12345       comment4
28366356    comment5,comment6
1212        comment7

2 个解决方案

#1

In SQL Server -- prior to the most recent version -- you would use a correlated subquery:

在SQL Server中——在最新版本之前——您将使用一个相关的子查询:

SELECT l.LoadId,
       STUFF( (SELECT ',' + comment -- AS 'data()'
               FROM TB_SOT_Comment c2
               WHERE c2.LoadID = l.LoadId
               FOR XML PATH ('')
              ), 1, 1, ''
            ) as comments
FROM (SELECT DISTINCT LoadId FROM TB_SOT_Comment) l;

Notes:

注:

The correlation clause (the inner WHERE) is key to this query.
相关子句(内部WHERE)是这个查询的关键。
I added STUFF() so the comments don't end in a comma.
我添加了STUFF()，所以注释不会以逗号结束。
The distinct loads come from a subquery. This is more efficient, because the subquery is run only once per load.
不同的负载来自子查询。这更有效，因为子查询每次加载只运行一次。
I commented out the AS 'data()'. To be honest, I simply haven't used that construct.
我将其注释为“data()”。老实说，我根本没有使用那个结构。

#2

Use Distinct Keyword For Remove duplicacy

使用不同的关键字删除重复

select distinct LoadId,(SELECT coment + ',' AS 'data()' FROM TB_SOT_Comment Where LoadID = t.LoadID FOR XML PATH('')) from TB_SOT_Comment t

#1