sql - 从 sql 表中动态选择不同的当前列和以前的列

Question

我有一个这样的临时表

   Id  |Name    |Status |   Rate |  Method  |ModifiedTime             |ModifiedBy
-----------------------------------------------------------------------------
    1  |Recipe1 |  0    |    30  |   xyz    | 2016-07-26 14:55:57.977 |     A
-------------------------------------------------------------------------------
    2  |Recipe1 |  0    |    30  |   abc    | 2016-07-26 14:56:18.123 |     A
--------------------------------------------------------------------------------
    3  |Recipe1 |  1    |    30  |   xyz    | 2016-07-26 14:57:50.180 |     b

我只想选择更改，并想显示该值之前的值以及当前值以及更改者的值。最终结果如下。我正在使用 SQL Server 2014。

Item    | Before |  After |ModifiedTime             | ModifiedBy
-----------------------------------------------------------------------------
Method  |  xyz   |  Abc   | 2016-07-26 14:56:18.123 |   A
-------------------------------------------------------------------------------
Status  |  0     |  1     | 2016-07-26 14:57:50.180 |   b
--------------------------------------------------------------------------------
Method  |  Abc   |  xyz   | 2016-07-26 14:57:50.180 |   b

我想动态地执行此操作，而不是像此链接中所示单独指定每个列名

Link

Answer 1

假设 NAME (Recipe1) 是一个键

Declare @Table table (Id int,Name varchar(50),Status int,Rate int,Method varchar(50),ModifiedTime DateTime,ModifiedBy varchar(50))
Insert Into @Table values
(1,'Recipe1',0,30,'xyz','2016-07-26 14:55:57.977','A'),
(2,'Recipe1',0,30,'abc','2016-07-26 14:56:18.123','A'),
(3,'Recipe1',1,30,'xyz','2016-07-26 14:57:50.180','b')

Declare @XML xml
Set @XML = (Select * from @Table for XML RAW)

;with cteBase as (
    Select ID           = r.value('@Id','int') 
          ,Name         = r.value('@Name','varchar(150)')
          ,ModifiedTime = r.value('@ModifiedTime','varchar(150)')
          ,ModifiedBy   = r.value('@ModifiedBy','varchar(150)')
          ,Item         = Attr.value('local-name(.)','varchar(max)')
          ,Value        = Attr.value('.','varchar(max)')
    From  @XML.nodes('/row') AS A(r)
    Cross Apply A.r.nodes('./@*[local-name(.)!="Id"]') AS B(Attr)
)
,cteExt as (Select *,LastValue =Lag(Value) over (Partition By Name,Item Order by ModifiedTime) From cteBase)
Select Name
      ,Item
      ,Before=LastValue
      ,After =Value
      ,ModifiedTime
      ,ModifiedBy
 From  cteExt 
 Where Value<>LastValue and LastValue is not null
   and Item not in ('ModifiedTime','ModifiedBy')
 Order By Name,ModifiedTime

返回

Name    Item    Before  After   ModifiedTime            ModifiedBy
Recipe1 Method  xyz     abc     2016-07-26T14:56:18.123 A
Recipe1 Method  abc     xyz     2016-07-26T14:57:50.180 b
Recipe1 Status  0       1       2016-07-26T14:57:50.180 b