有一表
用户id 原部门 新部门 调动原因
1 12 34 5
... 已知调动原因总共10种
希望能得到一个类似如下的表格
部门\调动原因 1 2 3 4 5 6 7 8 9 10
部门1 合计1 合计2 合计3 ... ...
部门2就是得到每个部门因为各种原因而调动的人数的统计急,在线等救命.......
用户id 原部门 新部门 调动原因
1 12 34 5
... 已知调动原因总共10种
希望能得到一个类似如下的表格
部门\调动原因 1 2 3 4 5 6 7 8 9 10
部门1 合计1 合计2 合计3 ... ...
部门2就是得到每个部门因为各种原因而调动的人数的统计急,在线等救命.......
insert into #
select 1,12,34,5
union all select 3,2,34,15
union all select 5,32,34,1
union all select 6,8,34,4
union all select 3,12,34,3
union all select 4,7,34,5
union all select 5,12,34,2
union all select 3,1,34,1
select * from #
declare @sql varchar(8000)
select @sql=''
select @sql=@sql+',isnull((select sum(1) from # where 原部门=a.原部门 and 调动原因 = '+cast(调动原因 as nvarchar(10))+'group by 原部门 ),'''') as '''+cast(调动原因 as nvarchar(100))+'''' from # group by 调动原因 order by 调动原因
select @sql='select 原部门 ''部门'''+@sql+' from # a group by 原部门 order by 原部门'
print @sql
exec(@sql)
drop table #
select custbh,nianyue ,sum(sl),sum(je)
from cpcks s ,cpckm m where m.billno=s.billno and patindex('%'+m.djlx+'%','销售出库-客户退货')>0 and left(nianyue,4)=@nian
group by custbh,nianyue order by custbh
insert into TempCustXSFX(custbh,nianyue,hjsl,hjje) select '合计' ,@nian+'01',sum(hjsl),sum(hjje) from TempCustXSFX where nianyue=@nian+'01'
insert into TempCustXSFX(custbh,nianyue,hjsl,hjje) select '合计' ,@nian+'02',sum(hjsl),sum(hjje) from TempCustXSFX where nianyue=@nian+'02'
insert into TempCustXSFX(custbh,nianyue,hjsl,hjje) select '合计' ,@nian+'03',sum(hjsl),sum(hjje) from TempCustXSFX where nianyue=@nian+'03'
insert into TempCustXSFX(custbh,nianyue,hjsl,hjje) select '合计' ,@nian+'04',sum(hjsl),sum(hjje) from TempCustXSFX where nianyue=@nian+'04'
insert into TempCustXSFX(custbh,nianyue,hjsl,hjje) select '合计' ,@nian+'05',sum(hjsl),sum(hjje) from TempCustXSFX where nianyue=@nian+'05'
insert into TempCustXSFX(custbh,nianyue,hjsl,hjje) select '合计' ,@nian+'06',sum(hjsl),sum(hjje) from TempCustXSFX where nianyue=@nian+'06'
insert into TempCustXSFX(custbh,nianyue,hjsl,hjje) select '合计' ,@nian+'07',sum(hjsl),sum(hjje) from TempCustXSFX where nianyue=@nian+'07'
insert into TempCustXSFX(custbh,nianyue,hjsl,hjje) select '合计' ,@nian+'08',sum(hjsl),sum(hjje) from TempCustXSFX where nianyue=@nian+'08'
insert into TempCustXSFX(custbh,nianyue,hjsl,hjje) select '合计' ,@nian+'09',sum(hjsl),sum(hjje) from TempCustXSFX where nianyue=@nian+'09'
insert into TempCustXSFX(custbh,nianyue,hjsl,hjje) select '合计' ,@nian+'10',sum(hjsl),sum(hjje) from TempCustXSFX where nianyue=@nian+'10'
insert into TempCustXSFX(custbh,nianyue,hjsl,hjje) select '合计' ,@nian+'11',sum(hjsl),sum(hjje) from TempCustXSFX where nianyue=@nian+'11'
insert into TempCustXSFX(custbh,nianyue,hjsl,hjje) select '合计' ,@nian+'12',sum(hjsl),sum(hjje) from TempCustXSFX where nianyue=@nian+'12' select custbh,sum(case nianyue when @nian+'01' then hjsl else 0 end) as a01 ,
sum(case nianyue when @nian+'02' then hjsl else 0 end) as a02 ,
sum(case nianyue when @nian+'03' then hjsl else 0 end) as a03 ,
sum(case nianyue when @nian+'04' then hjsl else 0 end) as a04 ,
sum(case nianyue when @nian+'05' then hjsl else 0 end) as a05 ,
sum(case nianyue when @nian+'06' then hjsl else 0 end) as a06 ,
sum(case nianyue when @nian+'07' then hjsl else 0 end) as a07 ,
sum(case nianyue when @nian+'08' then hjsl else 0 end) as a08 ,
sum(case nianyue when @nian+'09' then hjsl else 0 end) as a09 ,
sum(case nianyue when @nian+'10' then hjsl else 0 end) as a10 ,
sum(case nianyue when @nian+'11' then hjsl else 0 end) as a11 ,
sum(case nianyue when @nian+'12' then hjsl else 0 end) as a12,
sum(hjsl) as a13,
avg(hjsl) as a14, sum(case nianyue when @nian+'01' then hjje else 0 end) as b01 ,
sum(case nianyue when @nian+'02' then hjje else 0 end) as b02 ,
sum(case nianyue when @nian+'03' then hjje else 0 end) as b03 ,
sum(case nianyue when @nian+'04' then hjje else 0 end) as b04 ,
sum(case nianyue when @nian+'05' then hjje else 0 end) as b05 ,
sum(case nianyue when @nian+'06' then hjje else 0 end) as b06 ,
sum(case nianyue when @nian+'07' then hjje else 0 end) as b07 ,
sum(case nianyue when @nian+'08' then hjje else 0 end) as b08 ,
sum(case nianyue when @nian+'09' then hjje else 0 end) as b09 ,
sum(case nianyue when @nian+'10' then hjje else 0 end) as b10 ,
sum(case nianyue when @nian+'11' then hjje else 0 end) as b11 ,
sum(case nianyue when @nian+'12' then hjje else 0 end) as b12 ,
sum(hjje) as b13,
avg(hjje) as b14 from TempCustXSFX group by custbh order by custbh
GO