{"id":520,"date":"2014-08-05T10:03:30","date_gmt":"2014-08-05T08:03:30","guid":{"rendered":"http:\/\/www.rocworks.at\/wordpress\/?p=520"},"modified":"2014-08-05T10:03:30","modified_gmt":"2014-08-05T08:03:30","slug":"wincc-oa-pivot-query-to-combine-history-values-with-same-timestamp-in-one-row","status":"publish","type":"post","link":"https:\/\/www.rocworks.at\/wordpress\/?p=520","title":{"rendered":"WinCC OA pivot query to combine history values with same timestamp in one row…"},"content":{"rendered":"

This example combines the WinCC OA audit trail datapoint elements to a single row for one audit trail entry. <\/p>\n

select “TS”,”TIME”,”USERNAME”,”UINUM”,”BATCHID”,”ITEM”,”ITEMTYPE”,”ACTION”,”OLDVAL”,”NEWVAL”,”REASON”,”HOST” from (
\nselect h.ts, e.ELEMENT_NAME, NVL(to_char(h.VALUE_NUMBER),h.VALUE_STRING) val
\nfrom eventhistory h
\njoin elements e on e.ELEMENT_ID = h.ELEMENT_ID
\nwhere e.ELEMENT_NAME in (
\n‘System1:_AuditTrail.time’,
\n‘System1:_AuditTrail.username’,
\n‘System1:_AuditTrail.uinum’,
\n‘System1:_AuditTrail.batchid’,
\n‘System1:_AuditTrail.item’,
\n‘System1:_AuditTrail.itemtype’,
\n‘System1:_AuditTrail.action’,
\n‘System1:_AuditTrail.oldval’,
\n‘System1:_AuditTrail.newval’,
\n‘System1:_AuditTrail.reason’,
\n‘System1:_AuditTrail.host’
\n)
\n) a
\npivot (listagg(val) within group (order by element_name)
\nfor element_name in (
\n‘System1:_AuditTrail.time’ as time,
\n‘System1:_AuditTrail.username’ as username,
\n‘System1:_AuditTrail.uinum’ as uinum,
\n‘System1:_AuditTrail.batchid’ as batchid,
\n‘System1:_AuditTrail.item’ as item,
\n‘System1:_AuditTrail.itemtype’ as itemtype,
\n‘System1:_AuditTrail.action’ as action,
\n‘System1:_AuditTrail.oldval’ as oldval,
\n‘System1:_AuditTrail.newval’ as newval,
\n‘System1:_AuditTrail.reason’ as reason,
\n‘System1:_AuditTrail.host’ as host
\n));<\/p>\n","protected":false},"excerpt":{"rendered":"

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 … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4,3],"tags":[],"class_list":["post-520","post","type-post","status-publish","format-standard","hentry","category-oracle","category-wincc-oa"],"_links":{"self":[{"href":"https:\/\/www.rocworks.at\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/520","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.rocworks.at\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.rocworks.at\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.rocworks.at\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.rocworks.at\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=520"}],"version-history":[{"count":1,"href":"https:\/\/www.rocworks.at\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/520\/revisions"}],"predecessor-version":[{"id":521,"href":"https:\/\/www.rocworks.at\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/520\/revisions\/521"}],"wp:attachment":[{"href":"https:\/\/www.rocworks.at\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=520"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.rocworks.at\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=520"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.rocworks.at\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=520"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}