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

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

DECLARE @NewValues TABLE
(
    [VehicleID]             VARCHAR(255),
    [CustomerID]            VARCHAR(255),
    [RegistrationNumber]    VARCHAR(255),
    [FleetNumber]           VARCHAR(255),
    [EngineNumber]          VARCHAR(255)
)
INSERT INTO @NewValues([VehicleID], [CustomerID], [RegistrationNumber], [FleetNumber], [EngineNumber])
SELECT p.[VehicleID],p.[CustomerID],p.[RegistrationNumber],p.[FleetNumber], p.[EngineNumber]
FROM
(
  SELECT p.NewValue, p.FieldName
   FROM dbo.VehicleAudit AS p
) AS j
PIVOT
(
  MAX(NewValue) FOR FieldName IN ([VehicleID],[CustomerID],[RegistrationNumber],[FleetNumber],[EngineNumber])
) AS p;


DECLARE @OldValues TABLE
(
    [CustomerID]            VARCHAR(255),
    [RegistrationNumber]    VARCHAR(255),
    [FleetNumber]           VARCHAR(255),
    [EngineNumber]          VARCHAR(255)
)
INSERT INTO @OldValues([CustomerID], [RegistrationNumber], [FleetNumber], [EngineNumber])
SELECT d.[CustomerID],d.[RegistrationNumber],d.[FleetNumber], d.[EngineNumber]
FROM
(
  SELECT d.OldValue, d.FieldName
   FROM dbo.VehicleAudit AS d
) AS j
PIVOT
(
  MAX(OldValue) FOR FieldName IN ([CustomerID],[RegistrationNumber],[FleetNumber], [EngineNumber])
) AS d

Answer

You can use conditional aggregation:

select action, pk,
       max(case when FieldName = 'VehicleID' then NewValue end) as VehicleID,
       max(case when FieldName = 'VehicleMake' then NewValue end) as VehicleMake_NewValue,
       max(case when FieldName = 'ServiceInterval' then NewValue end) as ServiceInterval_NewValue,
       max(case when FieldName = 'VehicleMake' then OldValue end) as VehicleMake_OldValue,
       max(case when FieldName = 'ServiceInterval' then OldValue end) as ServiceInterval_OldValue
from t
group by pk, action;

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

Advertisement

Answer