Table1:
PID | Name | Qty |
---|---|---|
1 | 一个 | 3 |
1 | 二个 | 2 |
1 | 三个 | 2 |
2 | 四个 | 1 |
2 | 五个 | 3 |
需要的结果为:
PID | Names | Sum(Qty) |
---|---|---|
1 | 一个,二个,三个 | 7 |
2 | 四个,五个 | 4 |
SQL:
SELECT t.PID,
[Names] = stuff((select ',' + [Name] from table1 m where [PID] = t.[PID] for xml path('')) , 1 , 1 , ''),
SUM(t.Qty) AS Sum(Qty)
FROM table1 t
GROUP BY PID