Link to home
Start Free TrialLog in
Avatar of cyimxtck
cyimxtckFlag for United States of America

asked on

Pivot table with two tables

We have an audit table and a "live row" table which we are inheriting from another system.  The audit table only contains a couple of columns:
  ISSUE_NO     NUMBER(10)                       NOT NULL,
  UPDATE_DT    DATE                             NOT NULL,
  CHANGED_BY   VARCHAR2(30 BYTE),
  OLD_VALUE    VARCHAR2(2000 BYTE),
  NEW_VALUE    VARCHAR2(2000 BYTE),
  COLUMN_NAME  VARCHAR2(32 BYTE)

The "live table", which contains a cumulative adjustment from all the previous audit table rows, which are all linked together by the issue number.  Only one row will ever exist in the "live" table but there can be multiple rows in the audit table and even multiple rows with the same timestamp.  (this is because the legacy application saves everything you changed once when you click save)  In that instance we could use one row with every change for that time.

We need to figure out a way to get those two tables joined up such that the view or procedure or....that we create makes a detailed row for every audit table row and every active row.

We have not seen any examples out there on 10G (which we are stuck with corporate wide).  Surely this isn't rocket science for you expert PL/SQL folks?  Or is this something that is just known cannot be done?

Please tell me that this is reasonable to accomplish?

Thanks,

B
Avatar of GGuzdziol
GGuzdziol
Flag of Luxembourg image

What you are trying to achieve, can you provide some example? I.e. expected output would be perfect.
Avatar of cyimxtck

ASKER

The five rows of inserts listed below would illustrate what we are trying to achieve in every scenario.  You would end up with four rows in your "live row" table.  This is because there are two modifications that were made at the same time, therefore both of those are represented as a single row having both columns updated.
These inserts would create this in our "live" table:
 
ID ISSUE_STATUS_TYPE    RESOLUTION_DESC ISSUE_DESC           MODIFIED               CHANGED_BY
1   R                                           I FIXED THE PROBLEM TEST                       10/6/1999 10:01     XJ633GH
2   R                                           I FIXED THE PROBLEM TEST Problem      10/8/1999 10:40     RECONRW
3   C                                           I FIXED THE PROBLEM TEST Problem       11/17/1999 11:31  XS956PD
4   C                                           I FIXED THE PROBLEM TEST PROBLEM   11/18/1999 10:30   XJ633GH
We would like to have a view, materialized view, procedure, whatever way to get this data "pivoted" and into our base table.
Thoughts?
Thanks in advance,
B

SET DEFINE OFF;
Insert into ISSUE
   (ISSUE_NO, UPDATE_DT, CHANGED_BY, OLD_VALUE, NEW_VALUE, COLUMN_NAME)
 Values
   (41, TO_DATE('10/06/1999 10:01:28', 'MM/DD/YYYY HH24:MI:SS'), 'XJ633GH', 'A', 'R', 'ISSUE_STATUS_TYPE');
Insert into ISSUE
   (ISSUE_NO, UPDATE_DT, CHANGED_BY, NEW_VALUE, COLUMN_NAME)
 Values
   (41, TO_DATE('10/06/1999 10:01:28', 'MM/DD/YYYY HH24:MI:SS'), 'XJ633GH', 'I FIXED THE PROBLEM', 'RESOLUTION_DESC');
Insert into ISSUE
   (ISSUE_NO, UPDATE_DT, CHANGED_BY, OLD_VALUE, NEW_VALUE, COLUMN_NAME)
 Values
   (41, TO_DATE('10/08/1999 10:40:53', 'MM/DD/YYYY HH24:MI:SS'), 'RECONRW', 'TEST', 'TEST Problem', 'ISSUE_DESC');
Insert into ISSUE
   (ISSUE_NO, UPDATE_DT, CHANGED_BY, OLD_VALUE, NEW_VALUE, COLUMN_NAME)
 Values
   (41, TO_DATE('11/17/1999 11:31:57', 'MM/DD/YYYY HH24:MI:SS'), 'XS956PD', 'R', 'C', 'ISSUE_STATUS_TYPE');
Insert into ISSUE
   (ISSUE_NO, UPDATE_DT, CHANGED_BY, OLD_VALUE, NEW_VALUE, COLUMN_NAME)
 Values
   (41, TO_DATE('11/18/1999 11:01:07', 'MM/DD/YYYY HH24:MI:SS'), 'RECON', 'TEST Problem', 'TEST PROBLEM', 'ISSUE_DESC');
COMMIT;

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of GGuzdziol
GGuzdziol
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Finally someone has cracked this problem for me.  I was looking on asktom and saw that analytic functions there for this purpose but...

Thanks,

B
I just figured out one problem that I have in the previous example...   :(

Without the old_value, I can never retrieve the "original" row that was created in the database.  This would of course apply to ONLY the first value(s) for that partucular update date.

I can get it out with rownum I believe and try to collapse the dataset like that?

Please give me your thoughts.

Thanks,

B
Actually if you think about this you'll find out that if one attribute was *never* changed you won't be able to get its value. Assuming there was at least one change for each attribute - to get original row you can do something similiar to what you've just described. Probably you'd rather use row_number() and partition it by issue no and attribute name, order by date and pick old values for rows with row_number() = 1, but probably you see the idea :-)

Good luck!
Grzegorz.