2021-10-27 跨表行列转换建视图，视图数据同步更新到表

1、行列转换

使用unpriots

CREATE VIEW [dbo].[V_New1] AS
select unpriots.Time,unpriots.Name,unpriots.Value,T_BASE.Id, T_BASE.Type, T_BASE.Unit,T_BASE
from (select Time,

CAST(Ps AS VARCHAR(10)) as Ps,
CAST(Ua AS VARCHAR(10)) as Ua,
CAST(Ub AS VARCHAR(10)) as Ub,
CAST(Uc AS VARCHAR(10)) as Uc,
CAST(Ia AS VARCHAR(10)) as Ia,
CAST(Ib AS VARCHAR(10)) as Ib,
CAST(Ic AS VARCHAR(10)) as Ic,
CAST(Pa AS VARCHAR(10)) as Pa,
CAST(Pb AS VARCHAR(10)) as Pb,
CAST(Pc AS VARCHAR(10)) as Pc

from T_DATA
) as a
unpivot(
Value for  Name in
(

Ps,
Ua,
Ub,
Uc,
Ia,
Ib,
Ic,
Pa,
Pb,
Pc

)
) as unpriots
   INNER JOIN
                     dbo.T_BASE   ON unpriots.Name = T_BASE.Name

上述示例，翻译如下：创建V_New1的视图，视图有6列为：Time，Name，Value，Id，Type，Uint
从T_BASE中查询Id，Type，Uint
从T_DATA中查询Time，列Ps,Ua,Ub,Uc,Ia,Ib,Ic,Pa,Pb,Pc，列名称转换为Name列的行数据，列Ps,Ua,Ub,Uc,Ia,Ib,Ic,Pa,Pb,Pc，数据转换为Value列的行数据，Name和Value相对应。
T_BASE与T_DATA通过Name关联。

2、根据视图同步数据到表

将视图V_New1中最新的数据插入T_SDATA

  insert into [T_SDATA]   select * from [V_New1] t1

  where NOT EXISTS 
  (
      select * from  [T_SDATA] t2
      where t1.Time = t2.Time
  )

翻译如下：查询V_New1中Time与T_SDATA中Time，如果存在Time不同，将TIme不同的数据插入到T_SDATA。

设定任务，上述语句一分钟执行一次，那么将实现每分钟视图数据插入到数据库表。