sql server - Mapping row values of one table to column name of another table -

i have table keeps track of updates on 15 tables called 'tracking_table'. wanted 1 table 15 tables kept 10 columns in 'tracking_table' max values of no of cols in 15 tables.

now tracking_table i'm able latest updates done on particular column of particular table in following structure.

p_key_no    col_name    value               table __________________________________________________________________ 1       altemail    abc@gmail.com           emp_info 1       password    aa321                   emp_info 2       altemail    xyz@gmail.com           emp_info 2       email       pqr@yahoo.com           emp_info 2       password    sb12321                 emp_info 

this keep track of name of table, name of column, primary key value of particular row , changed value.

and emp_info table shown below:

pkey    email           fullname    password    time_stamp                  altemail         1       a123@xyz.com    xyz1        aa123       2013-04-05 13:24:49.650     aaa@gmail.com        2       b123@xyz.com    xyz2        bb123       2013-04-05 13:24:49.650     bbb@gmail.com        3       c123@xyz.com    xyz3        cc123       2013-04-05 13:24:49.650     ccc@gmail.com        

i want show emp_info table updated values of particular column only.

so please me map row values original table column name , value.

thanks in advance.

this can done several different ways, 1 way first pivoting tracking_table convert values rows columns , joining on emp_info table.

the pivot code similar following:

select p_key_no, altemail, password, email tracking_table pivot (   max(value)   col_name in (altemail, password, email) ) p [table] ='emp_info' 

see sql fiddle demo. data in rows can used data comparison emp_info table. final code similar to:

;with cte (   select p_key_no, altemail, password, email   tracking_table   pivot   (     max(value)     col_name in (altemail, password, email)   ) p   [table] ='emp_info' ) select e.pkey,   coalesce(c.email, e.email) email,   e.fullname,   coalesce(c.password, e.password) password,   time_stamp,   coalesce(c.altemail, e.altemail) altemail emp_info e left join cte c   on e.pkey = c.p_key_no; 

see sql fiddle demo. gives final result:

| pkey |         email | fullname | password |          time_stamp |      altemail | ------------------------------------------------------------------------------------ |    1 |  a123@xyz.com |     xyz1 |    aa321 | 2013-04-05 13:24:49 | abc@gmail.com | |    2 | pqr@yahoo.com |     xyz2 |  sb12321 | 2013-04-05 13:24:49 | xyz@gmail.com | |    3 |  c123@xyz.com |     xyz3 |    cc123 | 2013-04-05 13:24:49 | ccc@gmail.com | 

the pivot written using aggregate function case expression:

select p_key_no,    max(case when col_name = 'altemail' value end) altemail,   max(case when col_name = 'password' value end) password,   max(case when col_name = 'email' value end) email  tracking_table [table] ='emp_info' group p_key_no