compare two tables and get record new and old values

Question

I have two tables table A(master table)- 250 columns and table b(history table)- 240 columns, I need to create dynamic query as i table have 250 columns. using oracle

suppose:  Table A(Master table)

ID  Name    City    Phone   Category 
---------------------------------------
111 ABC    Delhi    123456  Red
112 BCD    Mumbai   234987  Green
113 CGU    PUNE     987456  Black

suppose:  Table B(History table)



 ID  City   Phone   Category 
 ---------------------------------
 111 Kerala 123456  White 
 113 Jammu  577756  Black

I need output something like that

ID  changes column name Old_value   new_value
----------------------------------------------------------
111         City        Kerala      Delhi
111         Category    White       Red
113         City        Jammu       Pune 
113         Phone       987456      577756

but This is not dynamic i am using oracle

select distinct 
  hist.ID, 
  'city' as Changed_Column, 
  hist.cityas Old_Value, 
  daily.cityas New_Value
from table A dly  daily 
inner join table b hist on daily.ID= hist.ID
  and daily.city <> hist.city

What happens when the columns are of different types? – Gordon Linoff Sep 16 '19 at 15:23 — Gordon Linoff, Sep 16 '19 at 15:23
yes... columns are different – kirti choudhary Sep 17 '19 at 13:03 — kirti choudhary, Sep 17 '19 at 13:03

score 2 · Answer 1 · answered Sep 16 '19 at 13:43

2

Use UNPIVOT to convert the columns to rows in each table and then join the two:

Oracle Setup:

CREATE TABLE TableA ( ID, Name, City, Phone, Category ) AS
SELECT 111, 'ABC', 'Delhi',  '123456', 'Red'   FROM DUAL UNION ALL
SELECT 112, 'BCD', 'Mumbai', '234987', 'Green' FROM DUAL UNION ALL
SELECT 113, 'CGU', 'PUNE',   '987456', 'Black' FROM DUAL;

CREATE TABLE TableB ( ID, City, Phone, Category ) AS
SELECT 111, 'Kerala', '123456', 'White' FROM DUAL UNION ALL
SELECT 113, 'Jammu',  '577756', 'Black' FROM DUAL

Query:

SELECT a.id,
       a.column_name,
       a.old_value,
       b.new_value
FROM   (
         SELECT id, column_name, old_value
         FROM   TableA
         UNPIVOT ( old_value FOR column_name IN ( City, Phone, Category ) )
       ) a
       INNER JOIN
       (
         SELECT id, column_name, new_value
         FROM   TableB
         UNPIVOT ( new_value FOR column_name IN ( City, Phone, Category ) )
       ) b
       ON ( a.id = b.id AND a.column_name = b.column_name )
WHERE a.old_value <> b.new_value

Output:

 ID | COLUMN_NAME | OLD_VALUE | NEW_VALUE
--: | :---------- | :-------- | :--------
111 | CITY        | Delhi     | Kerala   
111 | CATEGORY    | Red       | White    
113 | CITY        | PUNE      | Jammu    
113 | PHONE       | 987456    | 577756

db<>fiddle here

answered Sep 16 '19 at 13:43

MT0

143,790
11
59
117

@MTO . . . This assumes that all the values are strings. – Gordon Linoff Sep 16 '19 at 14:08
yes.. thanks, but i need dynamically as i have 250 columns – kirti choudhary Sep 16 '19 at 14:09
@GordonLinoff That is easy to fix - just convert all the columns into the same data type (i.e. a string) [db<>fiddle](https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=4abdb5e3daaec7f56bf77dbad1dc9af8) – MT0 Sep 16 '19 at 14:11
1

@kirtichoudhary You cannot use a [dynamic `PIVOT`](https://stackoverflow.com/questions/15491661/dynamic-pivot-in-oracle-sql) or `UNPIVOT` in SQL queries. You need to have a fixed set of columns - why not just list all 250 columns in the `UNPIVOT`'s `IN` list? The query will handle it. – MT0 Sep 16 '19 at 14:15
@MT0 . . . That could change the values in the columns. For instance, dates might lose their time component. – Gordon Linoff Sep 16 '19 at 15:02
@GordonLinoff No, they don't need to. That's what ISO8601 formatting is for (amongst many other options): `TO_CHAR( date_value, 'YYYY-MM-DD"T"HH24:MI:SS' )` – MT0 Sep 16 '19 at 15:05
1

@kirtichoudhary "this is not working" is not a constructive statement. Why is it not working for you and what are the errors/issues that have occurred? The answer I've given gives the expected output for the [MRE] in your question; so if you are having trouble applying it to your full data set then you need to look at what is different from your example in the question to your actual data and debug those parts. If you want an "other way" then you're going to have to clarify what it is that you want to get an "other way". – MT0 Sep 17 '19 at 13:09

compare two tables and get record new and old values

1 Answers1