如果amount列中的值后面有空单元格（如您之前所述），您可以使用row\ u number（）获取cte中的唯一数字，然后计算每行的sum（按新的唯一数字排序）（对于一个金额相同）。最后，在for xml path（）中使用cte。

--getting Sum as unique for every sequence (Amount and following values)
with cte AS
(

select Id,Amount,Number,sum(Amount)over(order by Number rows unbounded preceding)SumSeq,comment
from
 (
   select Id,Amount,comment,
   row_number()over(order by Id)Number from Table
 )x

)

 select id,max(Amount),ConcatComments from
  (
    select id,SumSeq,Amount, 
    stuff((select Concat(',',comment) 
    from cte cte1 where cte1.SumSeq=cte2.SumSeq 
    for xml path('')),1,2,'') ConcatComments from cte cte2
 )Z
 group by SumSeq,id,ConcatComments

可以使用string\u agg函数连接注解。像这样：

Select ID, MAX(Amount) as Amount, STRING_AGG(Comment, ', ') as Comment
From [YourTable]
Group By ID;

您的数据没有指定排序的列，但结果似乎取决于排序。如果确实有这样的列，则可以通过计算非- NULL 价值观 amount 对于每个 id ，然后聚合。那就是：

select id, grp,
       string_agg(comment, ', ') within group (order by <ordering column>) as comments
from (select t.*, count(amount) over (partition by id order by <ordering column>) as grp
      from t
     ) t
group by id, grp;