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

 

 

转载于:https://www.cnblogs.com/dthom/articles/1830898.html