从 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
链接
推荐答案
假设 NAME (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
相关文章