**求分组合计语句**

[bigmingming]

NO item price
DT11080900915 A费 4.00
DT11080900915 B费 0.00
DT11080900921 A费 260.00
DT11080900921 B费 130.00



要求合计成
序号 NO A费 B费 合计（A+B)
1 DT11080900915 4.00 0.00 4.00
2 DT11080900921 260.00 130.00 390.00

[Ekun_sky]

sql code

引用

with a(NO,item,price) as (select 'DT11080900915','A费',14 union all select 'DT11080900915','B费',0 union all select 'DT11080900921','A费',260 union all select 'DT11080900921','B费',130) select *,A费+B费 as 合计 from a pivot (sum(price) for item in(A费,B费)) c

[还在加载中灬]

最好是动态处理

DECLARE @SQL VARCHAR(MAX)
SET @SQL='SELECT ROW_NUMBER()OVER(ORDER BY item)RN,item'
SELECT @SQL=@SQL+',SUM(CASE WHEN item='''+item+'''THEN price ELSE 0 END)['+item+']'
FROM TB
GROUP BY item
SET @SQL=@SQL+'FROM TB GROUP BY item'
EXEC(@SQL)

TB换成你的表

[还在加载中灬]

调整一下

DECLARE @SQL VARCHAR(MAX)
SET @SQL='SELECT ROW_NUMBER()OVER(ORDER BY No)RN,* FROM(SELECT No'
SELECT @SQL=@SQL+',ISNULL(SUM(CASE WHEN item='''+item+'''THEN price END),0)['+item+']'
FROM TB
GROUP BY item
SET @SQL=@SQL+',ISNULL(SUM(price),0)[合计]'
SET @SQL=@SQL+'FROM TB GROUP BY No)T'
EXEC(@SQL)