Not sure this is possible but I’m hoping someone can point me in the right direction.
I have a database that audit logs when a record changes stage. What I need to do is consolidate this back up into a report that gives me the earliest audit record for each stage by Record ID. i.e. I end up with a report that looks like this.
+----+---------+---------+---------+ | ID | Stage1 | Stage2 | Stage3 | +----+---------+---------+---------+ | 1 | 1/10/20 | 1/10/20 | 3/10/20 | | 2 | 1/10/20 | 2/10/20 | 4/10/20 | | 3 | 2/10/20 | 2/10/20 | 3/10/20 | +----+---------+---------+---------+
I can do this with multiple select queries but as my stages are stored in a table I was wondering if there is a way to build this dynamically. This would also minimise maintenance in the future.
My “Audit_Log” table has the following columns:
- RecordID
- Stage
- Event_Date
My “Stages” are stored in a table called “Stages” column “Stage”.
Any suggestions would be really appreciated. Thanks
Advertisement
Answer
You can try to use operator TRANSFORM.
TRANSFORM <aggregate-function-expression> <select-statement> PIVOT <expression> [IN (<column-value-list>)]
where <aggregate-function-expression> is an expression created with one of the aggregate functions,
<select-statement> contains a GROUP BY clause, <column-value-list> is a list of required values expected to be returned by the PIVOT expression, enclosed in quotes and separated by commas. (You can use the IN clause to force the output sequence of the columns.)
If there’s one event_date’s value to RecordID and Stage:
TRANSFORM Max(Audit_Log.Event_Date)AS stage_date SELECT Audit_Log.RecordID FROM Audit_Log GROUP BY Audit_Log.RecordID PIVOT Audit_Log.Stage