Merge grouped rows

Question

I would like to know if this is possible from this table

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

group by ID, sum the price and combine the service identifier, something like this:

 ID Price ServiceID 1 40 11,12 2 120 11 

Quite simple to group by ID and summarize the price, but to concatenate the service identifiers, which bothers me.

Thank you for your participation.

Answer 1

Using:

 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 @tt group by t.id 

Output:

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