This example combines the WinCC OA audit trail datapoint elements to a single row for one audit trail entry.

select “TS”,”TIME”,”USERNAME”,”UINUM”,”BATCHID”,”ITEM”,”ITEMTYPE”,”ACTION”,”OLDVAL”,”NEWVAL”,”REASON”,”HOST” from (
select h.ts, e.ELEMENT_NAME, NVL(to_char(h.VALUE_NUMBER),h.VALUE_STRING) val
from eventhistory h
join elements e on e.ELEMENT_ID = h.ELEMENT_ID
where e.ELEMENT_NAME in (
‘System1:_AuditTrail.time’,
‘System1:_AuditTrail.username’,
‘System1:_AuditTrail.uinum’,
‘System1:_AuditTrail.batchid’,
‘System1:_AuditTrail.item’,
‘System1:_AuditTrail.itemtype’,
‘System1:_AuditTrail.action’,
‘System1:_AuditTrail.oldval’,
‘System1:_AuditTrail.newval’,
‘System1:_AuditTrail.reason’,
‘System1:_AuditTrail.host’
)
) a
pivot (listagg(val) within group (order by element_name)
for element_name in (
‘System1:_AuditTrail.time’ as time,
‘System1:_AuditTrail.username’ as username,
‘System1:_AuditTrail.uinum’ as uinum,
‘System1:_AuditTrail.batchid’ as batchid,
‘System1:_AuditTrail.item’ as item,
‘System1:_AuditTrail.itemtype’ as itemtype,
‘System1:_AuditTrail.action’ as action,
‘System1:_AuditTrail.oldval’ as oldval,
‘System1:_AuditTrail.newval’ as newval,
‘System1:_AuditTrail.reason’ as reason,
‘System1:_AuditTrail.host’ as host
));