# SQL关于行列转换

0

向请教大家一个思路，关于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铭 | 初学一级 | 园豆：8

您需要登录以后才能回答，未注册用户请先注册