将一个表的行值映射到另一个表的列名

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.

我有一个表跟踪15个名为'tracking_table'的表的更新。因为我想要所有15个表只有一个表,所以我在'tracking_table'中保留了10列,这是所有15个表中col的最大值。

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

现在,通过tracking_table,我可以在以下结构中获取在特定表的特定列上完成的最新更新。

p_key_no    col_name    value               table
__________________________________________________________________
1       ALTEMAIL    [e-mail]           emp_info
1       PASSWORD    AA321                   emp_info
2       ALTEMAIL    [e-mail]           emp_info
2       EMAIL       [e-mail]           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:

emp_info表如下所示:

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

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

我想显示emp_info表,只显示特定列的更新值。

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

所以请帮我把行值映射到原始表的列名和值。

Thanks in advance.

提前致谢。

1 个解决方案

#1

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.

这可以通过几种不同的方式完成,一种方法是首先转动tracking_table,它将值从行转换为列,然后加入emp_info表。

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:

请参阅SQL Fiddle with Demo。这将获取行中的数据,这些行可用于与emp_info表进行数据比较。最终代码类似于:

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

请参阅SQL Fiddle with Demo。这给出了最终结果:

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

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

也可以使用带有CASE表达式的聚合函数来编写数据透视表:

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