SQL Sql选择分组依据和字符串连接

Sql select group by and string concat

提问by Girish K G

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

在这里，我需要按 id 和金额总和进行分组，并使用相同的 id 和不同的网络名称连接字符串名称

采纳答案by Vikram

this will work with sql-server 2008

这将适用于 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 ;

回答by HATCHA

SELECT p1.ID,  
   STUFF(( SELECT ' and ' + NAME  
       FROM YourTable  p2  
      WHERE p2.ID = p1.ID  
      ORDER BY NAME  
        FOR XML PATH('') )  
    , 1, 5, '' ) AS Name,  
    sum(Amount)  
  FROM YourTable p1  
  GROUP BY ID  

From Vikram answer
I add stufffor my result.

从 Vikram 的回答中，
我为我的结果添加了一些东西。