27,579
社区成员
发帖
与我相关
我的任务
分享
with a(id,itemA,itemB,data) as(select 1,0 ,1,'2018-08-01' --临时数据表
union select 2,1 ,0,'2018-07-01'
union select 3,1, 0,'2018-07-01'
union select 4,1, 0,'2018-07-01'
union select 5,0 ,1,'2018-07-01')
--执行查询
--------------------------------语句----------------------------------------------------------------------------------------------
select *,isnull([1],0)+isnull([2],0)+isnull([3],0)+isnull([4],0)+isnull([5],0)+isnull([6],0)+isnull([7],0)+
isnull([8],0)+isnull([9],0)+isnull([10],0)+ isnull([11],0)+isnull([12],0) as 合计 from
(select name,month(data) as 月份,sum(case when name='itemA' then itemA when name='itemB' then itemB else itemA+itemB end) as a1
from a cross apply(select 'itemA' as name union all select 'itemb' union all select '合计') as b group by month(data),name)
as c pivot(sum(a1) for 月份
in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) as d
-------------------------------结束-----------------------------------------------------------------------------------------------
-- 例子
create table t ( id int,itemA int, itemB int,Date datetime)
go
insert t
select 1 ,0 ,1 ,'2018-08-01'
union select 2 ,1 ,0 ,'2018-07-01'
union select 3 ,1 , 0 ,'2018-07-01'
union select 4 ,1 , 0 ,'2018-07-01'
union select 5 ,0 ,1 ,'2018-07-01'
---
select col ,
sum([1]) [1],sum([2]) [2],sum([3]) [3],sum([4]) [4],sum([5]) [5],sum([6]) [6],
sum([7]) [7],sum([8]) [8],sum([9]) [9],sum([10])[10],sum([11])[11],sum([12]) [12]
from ( select col,MONTH([DATE]) m , sum(case when col ='itemA' then itemA else itemB end) val
from t cross apply (select 'itemA' col union select 'itemB') b
group by col,MONTH([DATE])
) t
pivot (sum(val) for m in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) a
group by rollup (col)
--
drop table t