sql server - Sql select group by and string concat

itemprop="text">

i have a table
like this

ID NAME
            Amount
1 cal 100
2 cal 200
3 cal
            300

4 cal 400
1 ser 500
2 ser 600
5
            ser 700

i want to
write a select query so that i wil get the resul like
this

ID NAME Amount
1
            cal and ser 600

2 cal and ser 800
3 cal 300
4
            cal 400
5 ser
            700

here i need to
group by id and sum of amount and concat the string name with same id and differnet
name

Answer

this will work with sql-server
2008

SELECT
            p1.ID,
 ( SELECT NAME + ' and ' 
 FROM YourTable p2
 WHERE
            p2.ID = p1.ID
 ORDER BY NAME
 FOR XML PATH('') ) AS Name,

            sum(Amount)
 FROM YourTable p1
 GROUP BY ID
            ;