Concatenate grouped rows

Question

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

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

ID   Price    ServiceID
1    40       11,12
2    120      11

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

Thank you for participating.

Why do you want to? This is considered by most to be a SQL Anti-Pattern; if we know why, we may be ale to recommend a different, better, approach... — MatBailie, Feb 04 '12 at 09:24
Duplicate of these: http://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server, http://stackoverflow.com/questions/5573528/string-concatenate-in-group-by-function-with-other-aggregate-functions, http://stackoverflow.com/questions/6579440/how-to-concatenate-all-strings-from-a-certain-column-for-each-group — J Cooper, Feb 04 '12 at 19:35

Kirill Polishchuk · Accepted Answer · 2012-02-04T08:35:54.280

12

Use:

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

Output:

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

edited Feb 04 '12 at 08:35

answered Feb 04 '12 at 08:29

Kirill Polishchuk

54,804
11
122
125

3

This does indeed work, however, for more complex examples, I get Column XYZ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. Any thoughts? – mattpm Oct 01 '13 at 05:08
@mattpm, Create a separate question. – Kirill Polishchuk Oct 01 '13 at 05:20

Concatenate grouped rows

1 Answers1

Linked

Related