SQL关于行列转换
0
悬赏园豆：5
[已关闭问题]
关闭于 2020-03-30 07:31
大家好：
向请教大家一个思路，关于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
