We help IT Professionals succeed at work.

Pivot table with two tables

cyimxtck
cyimxtck asked
on
994 Views
Last Modified: 2013-12-07
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
Comment
Watch Question

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

Author

Commented:
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

This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
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

Author

Commented:
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.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.