Hi,
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
test1
tranid | ename | eaddr
rams001 | pravin | pune
rams002 | kaka | nashik
and my transaction table as:-
Transaction table
tran1
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....
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
test1
tranid | ename | eaddr
rams001 | pravin | pune
rams002 | kaka | nashik
and my transaction table as:-
Transaction table
tran1
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....