Help coderanch get a
new server
by contributing to the fundraiser
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
  • Mikalai Zaikin

need help in pivot

Ranch Hand
Posts: 152
Eclipse IDE Chrome Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
i have one requirement where there is master table with some number of columns and i have a common transaction table for all masters with some columns as given below.

Master table
tranid | ename | eaddr
rams001 | pravin | pune
rams002 | kaka | nashik

and my transaction table as:-
Transaction table

tranid | tablename| fieldname | old_value | new_value
rams001 | test1 | eaddr | marol | pune
rams001 | test1 | ename | pravin shirke | pravin
rams002 | test1 | ename | mama | kaka

here as per my table structure value for tranid rams001 have changed for columns ename and eaddr. so there are two records in transaction table as shown above with previous and current value.
In, transaction table we are maintaining tablename and fieldname also to identify the master and the columns which were updated/changed. and my desired output is as follows:

transid |ename | eaddr | old_value | new_value
rams001 |pravin | pune | eaddr=marol;ename=pravin shirke | eaddr=pune;ename=pravin;
rams002 |kaka | nashik | ename=mama; | ename=kaka;

the values in old_value and new_value should be semicolon separated with column names as well only those columns which were changed.
i have used the below query but the ouput and the output does not match kindly help in acheiving what i want.

select t1.tranid,t1.ename,t1.eaddr,t2.old_value,t2.new_value from test1 t1, tran1 t2 where t1.tranid=t2.tranid;

i also thought of using pivot where we convert 'n' rows to columns dynamically any help in that also would be appreciated as i have never used pivot.
Please Help....
Ranch Hand
Posts: 312
MS IE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I believe, you need to understand a basic thing about SELECT statements, that is, they return rows of data.

Another thing is that, your table design of Transaction table is inelegant. There is no need for the new_value column as it will be present in the Master table !

After you have made the above change, your SELECT statement should look like the following.

SELECT t1.tranid, t1.ename, t1.eaddr, t2.tranid, t2.fieldname, t2.old_value FROM t1 Master, t2 Transaction WHERE t1.tranid = t2.tranid;

Executing the above query will result in two rows, one containing the eaddr old value and the other containing ename old value (in addition to the other columns).
That which doesn't kill us makes us stronger. I think a piece of pie wouldn't kill me. Tiny ad:
We need your help - Coderanch server fundraiser
    Bookmark Topic Watch Topic
  • New Topic