SQL Oracle - SUBQUERY to selectively pull data from *different* column, depending on table conditions

Jumping straight in. We have tables (TABLE) and audit tables (AUDITTABLE) that store changes to the main table.

In the table, we have entries like:

Changing the LocationID of row #1 from 35 to 50, and PersonID from 1 to 3 would be recorded in the audit table like (omitting some columns for brevity, there are also items like created and createdby to track when and by whom a value was changed):

I am writing a query where I am pulling data from the audit table:

SELECT
    sourceid AS ID,
    id AS EVENT_ID
    key AS COLUMN,
    oldvalue AS PREVIOUS_ENTRY
FROM
    AUDITTABLE

I want a sub-query within the select that pulls the currently stored value within the main table, depending upon the KEY from the audit table (which matches the main table column names one-for-one) for the current row. I can do this with a Case statement, but I have about 100 tables to do this for with each one having unique column names (and multiple dozens of columns each), which would be a nightmare to write.

EDIT

I'm looking at the recommended articles after writing this, and it seems like everything is pointing to writing case statements. If that's ultimately the answer, please just let me know this isn't doable in the manner I need, and no need to waste your time writing example case statements or unioned queries--that's well within my skillset, despite the physical agony that will ensue

I was thinking something like the below (which obviously doesn't work, but shows the general idea of what I'm trying to achieve).

SELECT
    sourceid AS ID,
    id AS EVENT_ID
    key AS COLUMN,
    oldvalue AS PREVIOUS_ENTRY,
    (SELECT TABLE. || (SELECT TEMPAUDITTABLE.KEY 
                       FROM AUDITTABLE AS TEMPAUDITTABLE 
                       WHERE TEMPAUDITTABLE.SOURCEID = AUDITTABLE.SOURCEID) 
     FROM Table 
     WHERE Table.ID = Audittable.sourceid) AS Current_Value
FROM
    AUDITTABLE

I know you can get table column names from USER_TAB_COLUMNS which may be part of the solution, but I have no idea how to implement something like that.

I apologize in advance if I'm missing something obvious. I'm fairly decent at writing queries, but I'm about 90% self-taught and there are likely some gaps in my knowledge.

To anyone willing to help point me in the right direction, thank you!

I've tried google, copilot, tech-on-the-net, and searching through stack overflow questions. I've also referenced the documentation for SQL Oracle.

You can use an XML trick to generate and run the dymamic SQL as part of a plain query, without your own PL/SQL function.

If you add a call to

DBMS_XMLGEN.GETXMLTYPE ('select "' || key || '" from table1 where id = ' || sourceid)

to your query it will give you the current value; but as part of an XML document. So you can use XMLQuery or XMLTable to extract it; this uses that expanded query as a CTE:

WITH cte AS (
    SELECT
        sourceid AS ID,
        id AS EVENT_ID,
        key AS COLUMN_NAME,
        oldvalue AS PREVIOUS_ENTRY,
        DBMS_XMLGEN.GETXMLTYPE (
            'select "' || key || '" from table1 where id = ' || sourceid) AS XML
    FROM
        AUDITTABLE
)
SELECT
    ID,
    EVENT_ID,
    COLUMN_NAME,
    PREVIOUS_ENTRY,
    CURRENT_VALUE
FROM
    cte
CROSS APPLY XMLTABLE (
    '/ROWSET/ROW'
    PASSING cte.XML
    COLUMNS CURRENT_VALUE number PATH '.'
)

with the result as a number, which is OK for these two values but not generically; you can change the data type from number to vachar2 in the XMLTable call. And if there might not be a current value you can use OUTER APPLY instead of CROSS APPLY.

Or more directly with XMLQuery:

SELECT
    sourceid AS ID,
    id AS EVENT_ID,
    key AS COLUMN_NAME,
    oldvalue AS PREVIOUS_ENTRY,
    XMLQUERY ('/ROWSET/ROW/*/text()'
        PASSING DBMS_XMLGEN.GETXMLTYPE (
            'select "' || key || '" from table1 where id = ' || sourceid)
        RETURNING CONTENT) AS CURRENT_VALUE
FROM
    AUDITTABLE

which returns the value as a string. You can convert that, but if it need to be generic, a string is probably your only option - as the current_value can't be more than one data type. That's going to potentially give you issues with dates etc. That would be true for a case expression too, of course, but at least then you could apply specific formatting for each key/column.

fiddle

PaulW's function approach might scale and perform better - not least because it allows the (hopefully PK) ID to be provided as a bind variable, reducing hard parsing (probably a lot).

A case expression might be more practical that either approach though, once created; and you can generate that from the data dictionary if you don't want to type it all. Presumably the audit table is being populated from a trigger, which would have the reverse problem, so you might be able to see how it was tackled there - including how triggers are generated and maintained.