CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008-r2pivot
whatwhatwhat
whatwhatwhat

Reputation: 2276

How to PIVOT with 2 grouping columns in the result set?

I have a query that outputs the following:

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
...

I need to PIVOT so that it returns this:

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

I tried this, but it gave an error:

Msg 8114, Level 16, State 1, Line 413 Error converting data type nvarchar to datetime. Msg 473, Level 16, State 1, Line 413 The incorrect value "Day1" is supplied in the PIVOT operator.

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

Upvotes: 0

Views: 110

Answers (2)

SteveC
SteveC

Reputation: 6015

This approach uses datediff's on the minimum date and the AppDate.

;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;

Upvotes: 0

GMB
GMB

Reputation: 222652

Since you expect a fixed number of columns, we don't necessarily need dynamic SQL. One option uses conditional aggregation... and lot of repeated typing:

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

Upvotes: 3

Related Questions