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

XXXXXXXX 32 2019-02-28 09:45:21

CCCCCCC 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