如何在结果集中使用2个分组列进行PIVOT

Question

我有一个输出以下内容的查询：

ApptDate    Truck_ID   Item    Qty
'8-19-20'   TruckA     ItemA   100
'8-19-20'   TruckB     ItemA   200
'8-20-20'   TruckC     ItemB   300
'8-20-20'   TruckD     ItemB   400
...

我需要PIVOT，这样它就会返回这个：

Item    Truck_ID    Day1    Day2 ... Day14
ItemA   TruckA      100     0        0
ItemA   TruckB      200     0        0
ItemB   TruckC      0       300      0
ItemB   TruckD      0       400      0

我试过这个，但它给出了一个错误：

消息8114，级别16，状态1，第413行将数据类型nvarchar转换为datetime时出错。消息473，级别16，状态1，第413行错误的值"；第1天"；在PIVOT操作符中提供。

select
item, truck_id, Day1, Day2, Day3, Day4, Day5, Day6, Day7, Day8, Day9, Day10, Day11, Day12, Day13, Day14 
from(
select
ds.ApptDate
, c.truck_id
, c.item 
, sum(c.qty) qty
from
maintable c with(nolock)
inner join secondtable ds with(nolock) on c.truck_id = ds.truckid and ds.type = 'O'
where
ds.apptdate between cast(getdate() as date) and dateadd(day, 14, cast(getdate() as date))
and coalesce(ds.CancelTruck, 0) <> 1
and ds.Status <> '5'
group by
c.truck_id
, c.item
, ds.ApptDate
) sourcetable
pivot
(
sum(qty)
for apptdate in ([Day1], [Day2], [Day3], [Day4], [Day5], [Day6], [Day7], [Day8], [Day9], [Day10], [Day11], [Day12], [Day13], [Day14])
) as pivottable

Answer 1

由于需要固定数量的列，所以我们不一定需要动态SQL。一个选项使用条件聚合。。。以及大量重复打字：

select
item,
truck_id,
sum(case when appt_date = cast(getdate() as date)                    then qty else 0 end) day0,
sum(case when appt_date = dateadd(day, -1 , cast(getdate() as date)) then qty else 0 end) day1,
sum(case when appt_date = dateadd(day, -2 , cast(getdate() as date)) then qty else 0 end) day2,
...
sum(case when appt_date = dateadd(day, -14, cast(getdate() as date)) then qty else 0 end) day14
from ( -- your current query here --) t
group by item, truck_id

Answer 2

这种方法在最小日期和AppDate上使用datediff。

;with 
min_dt_cte(min_dt) as (select min(cast(AppDate as date)) from MyTable),
pvt_dt_cte(ApptDate, Truck_ID, Item, Qty, DayNum) as (
select t.*, datediff(d, mdc.min_dt, cast(AppDate as date))
from min_dt_cte mdc
cross join
MyTable t)
select
pdc.Item, pdc.Truck_ID, 
iif(pdc.DayNum=1, Qty, 0) Day1,
iif(pdc.DayNum=2, Qty, 0) Day2,
...
iif(pdc.DayNum=14, Qty, 0) Day14
from
pvt_dt_cte pdc;