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

问题描述

限时删除！！

我有一个像这样的临时表

I have a temporary table like so

   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.

I would like to select only the changes and wanted to show what the value was previously and what it is currently accompanied by who changed it. The final outcome will be as follows. I am using 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

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

I would like to do this dynamically instead of specifying each column name individually as shown in this link

链接

推荐答案

假设名称(Recipe1)是密钥

Assuming NAME (Recipe1) is a key

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

这篇关于从sql表中动态选择不同的当前列和先前列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

1403页，肝出来的..