参见英文答案 >
How to concatenate text from multiple rows into a single text string in SQL server?43个
我有一张这样的桌子
我有一张这样的桌子
- ID NAME Amount
- 1 cal 100
- 2 cal 200
- 3 cal 300
- 4 cal 400
- 1 ser 500
- 2 ser 600
- 5 ser 700
我想写一个选择查询,这样我就会得到这样的结果
- ID NAME Amount
- 1 cal and ser 600
- 2 cal and ser 800
- 3 cal 300
- 4 cal 400
- 5 ser 700
解决方法
这将适用于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 ;
