明细数据汇总合计（列转行+行列汇总）

原数据

declare @PivotField varchar(max),@pivotField_sum varchar(max),@count int=0,@sql varchar(max)
select @PivotField = stuff((select ', '+ '[' + asset_classification + ']'  from t_card_registration_of_fixed_assets 
group by asset_classification
order by asset_classification for xml path('') ),1,1,''), 
		@pivotField_sum = stuff((select ', '+ 'sum(isnull([' + asset_classification + '],0)) as [' + asset_classification + ']' 
from t_card_registration_of_fixed_assets
group by asset_classification
order by asset_classification for xml path('') ),1,1,'')

select @count=count(*) from t_card_registration_of_fixed_assets

if @count>0
begin
set @sql='with cte
as
(select asset_classification,class_name, sum(original_value) as original_value from t_card_registration_of_fixed_assets
group by asset_classification,class_name), 
pivot_detail
as
(
SELECT [class_name],'+@PivotField+'
FROM 
cte p
PIVOT
(
sum(original_value)
FOR asset_classification IN
(  '+@PivotField+')
) AS pvt
),
piovt_detail_total
as
(
select dt.*, tot.tot_amount 
from 
(
SELECT [class_name],   '+@PivotField+'
FROM cte p
PIVOT
(
sum(original_value)
FOR asset_classification IN
(  '+@PivotField+')
) AS pvt
) dt left join 
(
select [class_name], sum(original_value) as tot_amount 
	from cte
	group by [class_name]
) tot on dt.[class_name] = tot.[class_name]
)

select [class_name] as 公司名称,'+@PivotField+',tot_amount as 共计 from piovt_detail_total
union all 
select ''总计'' as 公司名称,'+@pivotField_sum+', sum(tot_amount) as tot_amount
from piovt_detail_total'
end
else if @count=0
begin
set @sql='select ''未查询到数据!'' as 提示'
end
print @sql
exec(@sql)

效果如图