连接分组行 [英] Concatenate grouped rows

问题描述

我想知道从这张表中是否有可能

I would like to know is it possible from this table

ID   Price    ServiceID
1    50       11
1    -10      12
2    100      11
2    20       11

按 ID 分组，汇总价格并连接 serviceID，如下所示:

to group by ID, sum the price and concatenate serviceID, something like this:

ID   Price    ServiceID
1    40       11,12
2    120      11

按 ID 分组并汇总价格相当简单，但将服务 ID 连接起来是我的困扰.

It is rather simple to group by ID and sum the price, but concatenate service IDs what is troubling me.

感谢您的参与.

推荐答案

使用:

select t.id, sum(t.price)
    , stuff((
        select distinct ',' + cast(t2.ServiceID as varchar(max))
        from @t t2
        where t2.id = t.id
        for xml path('')
    ), 1, 1, '')
from @t t
group by t.id

输出:

----------- --------------------- ---------------------
1           40,00                 11,12
2           120,00                11

这篇关于连接分组行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！