I could use some help with creating a PIVOT table.
The table that I’m looking to pivot from is:
Track Number Customer Number Ship Date Op Code Curr Surcharge Reason Surcharge Amt 1100411007 326740721 2019-08-27 Ground USD Ground Fuel Surcharge 0.55 1100411007 126270721 2019-12-11 Ground USD Air Fuel Surcharge 0.45 1150481637 126170723 2019-01-19 Ground USD Residential Delivery 0.55 1150354456 125770746 2019-03-19 Ground USD Ext. Residential Del. 0.57 1150394131 126720765 2019-09-27 Ground USD Out of Area Delivery 0.50 1600746152 121770100 2019-03-12 Ground USD Ground Fuel Surcharge 0.52
Where I want to change it into the Format of
Track Number Surcharge Reason #1 Surcharge Reason #2 Surcharge Reason #3 Surcharge reason #N Tracking #1 Surcharge Amt (if any) Surcharge Amt (if any) Surcharge Amt (if any) ... Tracking #2 Surcharge Amt (if any) Surcharge Amt (if any) Surcharge Amt (if any) ... Tracking #3 ... ... ... ... Tracking #4 ... ... ... ... Tracking #5 ... ... ... ... ...
Which has the tracking number as the Row Identifier, the added columns are the Unique Values in the ‘Surcharge Reason’ column, and the values of the data are the Surcharge Amount if there is any for that Track Number & Surcharge Reason
Any help on sending me down the right path would be very much appreciated.
Advertisement
Answer
You can achieve your expected output using case statement. here is an example.
select
trackNumber,
sum(case when surchargeReason = 'Ground Fuel Surcharge' then amount else 0 end) as "Ground Fuel Surcharge"
from youTable
group by
trackNumber