Sql select group by and string concat

sql, sql-server, sql-server-2008, t-sql

Solution

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 ;

Problem

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

Original source

Related problems