sql 我的交叉表+行合计+列小计



select b.DTLBRAND,c.trxTypeName,SUM(ABS(b.DTLACTQTY)) as 'dtlactqty' into #temp from INV_TRX_DTL_HIS as b,INV_TRX_HEAD_HIS as a,TrxTypeForm as c
where a.HEADTRXTYPE=c.trxType and a.HEADTRXNO=b.DTLTRXNO and b.DTLFROMSUB='STK'
and (a.HEADTRXTYPE='RS' OR a.HEADTRXTYPE='RD' OR a.HEADTRXTYPE='SS' OR a.HEADTRXTYPE='RL' OR
a.HEADTRXTYPE='WO' OR a.HEADTRXTYPE='SK' OR a.HEADTRXTYPE='MF' OR a.HEADTRXTYPE='MA')
group by b.DTLBRAND,c.trxTypeName

declare @sql varchar(8000)
set @sql = 'select 
 case when grouping(DTLBRAND)=1 then ''合计'' else DTLBRAND end ''品牌'', '
select @sql = @sql +  'sum(case trxTypeName when '''+trxTypeName+''' 
                          then dtlactqty else 0 end) as '''+trxTypeName+''','
 from (select distinct trxTypeName from #temp) as a
select @sql = left(@sql,len(@sql)-1) + ',sum(dtlactqty) as ''小计'' from #temp  group by rollup(DTLBRAND) 
'
exec(@sql)
drop table #temp