CODEWITHSUNDEEP
sql-servert-sql
Rohit
Rohit

Reputation: 31

Mapping row values of one table to column name of another table

i have table which keeps track of updates on 15 tables called 'tracking_table'. As i wanted only one table for all 15 tables i kept 10 columns in 'tracking_table' which is max values of no of cols in all 15 tables.

Now from tracking_table i'm able to get the latest updates done on particular column of particular table in following structure.

p_key_no    col_name    value               table
__________________________________________________________________
1       ALTEMAIL    [email protected]           emp_info
1       PASSWORD    AA321                   emp_info
2       ALTEMAIL    [email protected]           emp_info
2       EMAIL       [email protected]           emp_info
2       PASSWORD    SB12321                 emp_info

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

And emp_info table is as shown below:

PKEY    EMAIL           FULLNAME    PASSWORD    TIME_STAMP                  ALTEMAIL        
1       [email protected]    xyz1        AA123       2013-04-05 13:24:49.650     [email protected]       
2       [email protected]    xyz2        BB123       2013-04-05 13:24:49.650     [email protected]       
3       [email protected]    xyz3        CC123       2013-04-05 13:24:49.650     [email protected]

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

So please help me to map row values to original table column name and value.

Thanks in advance.

Upvotes: 1

Views: 11313

Answers (1)

Taryn
Taryn

Reputation: 247720

This can be done several different ways, one way is by first pivoting the tracking_table which will convert the values from rows into columns and then joining on your emp_info table.

The pivot code will be similar to the following:

select p_key_no, ALTEMAIL, PASSWORD, EMAIL
from tracking_table
pivot
(
  max(value)
  for col_name in (ALTEMAIL, PASSWORD, EMAIL)
) p
where [table] ='emp_info'

See SQL Fiddle with Demo. This get the data in rows that can be used for data comparison with the emp_info table. The final code will be similar to:

;with cte as
(
  select p_key_no, ALTEMAIL, PASSWORD, EMAIL
  from tracking_table
  pivot
  (
    max(value)
    for col_name in (ALTEMAIL, PASSWORD, EMAIL)
  ) p
  where [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
from emp_info e
left join cte c
  on e.pkey = c.p_key_no;

See SQL Fiddle with Demo. This gives a final result:

| PKEY |         EMAIL | FULLNAME | PASSWORD |          TIME_STAMP |      ALTEMAIL |
------------------------------------------------------------------------------------
|    1 |  [email protected] |     xyz1 |    AA321 | 2013-04-05 13:24:49 | [email protected] |
|    2 | [email protected] |     xyz2 |  SB12321 | 2013-04-05 13:24:49 | [email protected] |
|    3 |  [email protected] |     xyz3 |    CC123 | 2013-04-05 13:24:49 | [email protected] |

The pivot could also be written using an aggregate function with a CASE expression:

select p_key_no, 
  max(case when col_name = 'ALTEMAIL' then value end) ALTEMAIL,
  max(case when col_name = 'PASSWORD' then value end) PASSWORD,
  max(case when col_name = 'EMAIL' then value end) EMAIL 
from tracking_table
where [table] ='emp_info'
group by p_key_no

Upvotes: 3

Related Questions