How to find the changes happened between rows?

Question

I have two tables that I need to find the difference between.

What's required is a table of a summary of what fields have changed (ignoring id columns). Also, I don't know which columns have changed.

e.g. Source table [fields that have changed are {name}, {location}; {id} is ignored]

id || name || location || description

1 || aaaa || ddd || abc

2 || bbbb || eee || abc

e.g. Output Table [outputting {name}, {location} as they have changed]

Table_name || Field_changed || field_was || field_now

Source table || name || aaaa ||  bbbb

Source table || location || ddd || eee

I have tried to use lag(); but that only gives me the columns I selected. Eventually I'd want to see all changes in all columns as I am not sure what columns are changed.

Also please note that the table has about 150 columns - so one of the biggest issues is how to find the ones that changed

Answer 1

As your table can contain multiple changes in a single row and it needs to be calculated in the result as multiple rows, I have created a query to incorporate them separately as follows:

WITH DATAA(ID, NAME, LOCATION, DESCRIPTION)
AS
(SELECT 1, 'aaaa', 'ddd', 'abc' FROM DUAL UNION ALL
SELECT 2, 'bbbb', 'eee', 'abc' FROM DUAL),

-- YOUR QUERY WILL START FROM HERE
CTE AS (SELECT NAME, 
LAG(NAME,1) OVER (ORDER BY ID) PREV_NAME,
LOCATION, 
LAG(LOCATION,1) OVER (ORDER BY ID) PREV_LOCATION,
DESCRIPTION, 
LAG(DESCRIPTION,1) OVER (ORDER BY ID) PREV_DESCRIPTION 
FROM DATAA)
--
SELECT
    'Source table' AS TABLE_NAME,
    FIELD_CHANGED,
    FIELD_WAS,
    FIELD_NOW
FROM
    (
        SELECT
            'Name' AS FIELD_CHANGED,
            PREV_NAME        AS FIELD_WAS,
            NAME   AS FIELD_NOW
        FROM
            CTE
        WHERE
            NAME <> PREV_NAME
        UNION ALL
        SELECT
            'location' AS FIELD_CHANGED,
            PREV_LOCATION        AS FIELD_WAS,
            LOCATION   AS FIELD_NOW
        FROM
            CTE
        WHERE
            LOCATION <> PREV_LOCATION
        UNION ALL
        SELECT
            'description' AS FIELD_CHANGED,
            PREV_DESCRIPTION        AS FIELD_WAS,
            DESCRIPTION   AS FIELD_NOW
        FROM
            CTE
        WHERE
            DESCRIPTION <> PREV_DESCRIPTION
    );

Output:

DEMO

Cheers!!