首页新闻找找看学习计划

SQL关于行列转换

0
悬赏园豆:5 [待解决问题]

大家好:

  向请教大家一个思路,关于SQL行列转换的统计,在组合最终格式时卡住了:)

1.基础序列

2.目前思路

 

2.1.排出基础统计,按照相同Code,时间 BackDate,同一BackType (已实现)

2.2.保留BackDate 序列 (已实现)

2.3 在BackDate下组合最终格式 (未实现)

3.实现效果

 

--临时表(基础序列)
create table #tmpS(BackDate varchar(10),BackType int,BackMoney decimal(18,2),code varchar(50))
insert #tmpS
select '2016-02-29',8,'11.00','B12' union all
select '2016-03-02',9,'16.00','B12' union all
select '2016-03-02',7,'17.00','B12' union all
select '2016-03-04',7,'17.00','B12' union all
select '2016-03-05',9,'15.00','B12' union all
select '2016-03-06',7,'12.00','B12' union all
select '2016-02-27',7,'12.00','B18' union all
select '2016-02-27',7,'12.00','B18'
--  实现效果(终)
select 
'B12' as Code,'2016-03-02' as BackDate
,7 as BackType,17.00 as BackMoney
,8 as BackType,0.00 as BackMoney
,9 as BackType,16.00 as BackMoney  union all
select 
'B12' as Code,'2016-02-29' as BackDate
,7 as BackType,11.00 as BackMoney
,8 as BackType,0.00 as BackMoney
,9 as BackType,0.00 as BackMoney  union all
select
'B18' as Code,'2016-02-27' as BackDate
,7 as BackType,24.00 as BackMoney
,8 as BackType,0.00 as BackMoney
,9 as BackType,0.00 as BackMoney

----实现思路:排出基础统计,按照相同Code,时间 BackDate,同一BackType

select code,BackDate,BackType,SUM(BackMoney)tBM from #tmpS
group by code,BackDate,BackType

  --A: 保留时间序列,在组合格式上卡住了
             
      select * from (
      select Code, BackDate,BackType ,BackMoney from  #tmpS
        ) t1
      PIVOT (SUM(BackMoney) for BackDate IN ([2016-02-29],[2016-03-02],[2016-03-04])) a
Frank铭的主页 Frank铭 | 初学一级 | 园豆:3
提问于:2016-03-06 22:24
< >
分享
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册