Skip to content
Advertisement

SQL Pivot when there is no matching field

I have the following table structure which generated using a trigger. Action describes insert or update.

AuditID PKColname PK FieldName OldValue NewValue Action
1 VehicleID 1 VehicleID 1 Insert
2 VehicleID 1 VehicleMake Mercedes Insert
3 VehicleID 1 VehicleMake Mercedes Toyota Update
4 VehicleID 2 VehicleMake Ferrari Aston Martin Update
5 VehicleID 2 ServiceInterval 25000KM 50000KM Update

My knowledge with pivot table is very limited how can I get the following output.

PK Action VehicleID VehicleMake_NewValue ServiceInterval_NewValue VehicleMake_OldValue ServiceInterval_OldValue
1 Insert 1 Mercedes NULL NULL NULL
1 Update NULL Toyota NULL Mercedes NULL
2 Update NULL Aston Martin 50000KM Ferrari 25000KM

What I have done is using this

Advertisement

Answer

You can use conditional aggregation:

Here is a db<>fiddle showing that it returns the results you specify in the question.

User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement