首页新闻找找看学习计划

sql server日期行转列

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

DECLARE @StarDate varchar(50),@EndDatevarchar(50)
DECLARE @MC varchar(6),@FAC int
set @StarDate='2019-02-25 08:00:00'
set @EndDate='2019-03-25 07:59:59'
set @MC='2000'
set @FAC=8

with tempb AS (
select convert(varchar(100),p.SaveDate,112) as DayTime ,isnull(sum(p.NetWeight/1000),0) as NetWeight,c.CustomerId
from Base_PoundRecord p
left join Base_Customers c on c.CustomerId=p.CustomerId
where p.CRState = 9 AND (p.SaveDate BETWEEN @StarDate and @EndDate) AND p.FactoryId=@FAC and p.MaterialCode=@MC and c.IsFinancial=1 and c.DeleteMark=1
group by SaveDate,p.NetWeight,c.CustomerId
)
SELECT CustomerId,DayTime,NetWeight FROM tempb

--卡住的位置
PIVOT (sum(NetWeight) as NetWeight FOR DayTime in(这里不知道该如何去构造日期))

查询后得到的结果是
CustomerId NetWeight DayTime
XXXXXXXX 31 2019-02-25 08:15:12
XXXXXXXX 32 2019-02-25 09:45:21
XXXXXXXX 31.2 2019-02-26 10:14:17
XXXXXXXX 33.1 2019-02-26 13:51:11
XXXXXXXX 34.1 2019-02-27 16:43:08
BBBBBBBB 32 2019-02-25 09:45:21
BBBBBBBB 32 2019-02-25 09:45:21
XXXXXXXX 32 2019-02-28 09:45:21
CCCCCCC 32 2019-02-25 09:45:21
CCCCCCC 32 2019-02-25 09:45:21
DDDDDDD 32 2019-02-25 09:45:21
DDDDDDD 32 2019-02-25 09:45:21
BBBBBBB 32 2019-02-26 09:45:21

想要得到的结果是,那一天没有的用0替代
CustomerId 2019-02-25 2019-02-26 2019-02-27 2019-02-28..........2019-03-25
XXXXXXXX 63 64.3 34.1 32 0
BBBBBBBB 64 32 0 0 0
CCCCCCC 64 0 0 0 0
DDDDDDD 64 0 0 0 0

尽量能够让SQL运行效率高些

wavegui的主页 wavegui | 初学一级 | 园豆:80
提问于:2019-03-29 16:15
< >
分享
所有回答(1)
0

问题自己解决

wavegui | 园豆:80 (初学一级) | 2019-04-02 06:58
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册