Sql-Server
如何在一行中分組/顯示相同的日期數據?
我有一個表格,每晚都會插入一個表格,它是數據的快照。在任何時間點,列中的數據都可能發生變化(AccountNo 保持不變,RunKey 增加 1,RunDate 增加 1 天;所有其他列都可以臨時更改)。以下是數據外觀的範例:
|AccountNo | RunKey | RunDate | Address | Salary | PromotionDate| ---------------------------------------------------------------------------- | 12345 | 2 | 06/20/2017 | 123 Main Street | 60,000 | 01/15/2017 | | 12345 | 3 | 06/21/2017 | 123 Main Street | 60,000 | 01/15/2017 | | 12345 | 4 | 06/22/2017 | 123 Main Street | 65,000 | 06/21/2017 |
每列更改都會有一條新記錄。因此,如果多個列發生更改,則每個更改都將記錄在一個新行中。我能夠動態地將僅更改的列插入到新表中。
Declare @YourTable Table ([AccountNo] int,[RunKey] int,[RunDate] date,[Address] varchar(50),[Salary] int,[PromotionDate] date) Insert Into @YourTable Values (12345,2,'06/20/2017','123 Main Street',60000,'01/15/2017') ,(12345,3,'06/21/2017','123 Main Street',60000,'01/15/2017') ,(12345,4,'06/22/2017','123 Main Street',65000,'06/21/2017') ;with cte as ( Select A.AccountNo ,A.RunKey ,A.RunDate ,B.* ,PreValue=Lag(Value) over (Partition By AccountNo,Item Order by RunDate) ,PreDate =Lag(RunDate) over (Partition By AccountNo,Item Order by RunDate) From @YourTable A Cross Apply ( values ('Address' ,cast(A.[Address] as varchar(max))) ,('Salary' ,cast(A.[Salary] as varchar(max))) ,('PromotionDate',cast(A.[PromotionDate] as varchar(max))) ) B (Item,Value) ) Select * From cte Where Value<>PreValue and PreValue is not null
這是我需要幫助的地方,我不知道如何在一行而不是多行中顯示同一日期、accountNo 和 RunKey 的所有更改。這有可能實現嗎?需要/需要類似結果的樣本:
如果我正確理解這就是您要查找的內容:
Declare @YourTable Table ([AccountNo] int,[RunKey] int,[RunDate] date,[Address] varchar(50),[Salary] int,[PromotionDate] date) Insert Into @YourTable Values (12345,2,'06/20/2017','123 Main Street',60000,'01/15/2017') ,(12345,3,'06/21/2017','123 Main Street',60000,'01/15/2017') ,(12345,4,'06/22/2017','123 Main Street',65000,'06/21/2017'); with ct as ( select A.AccountNo ,A.RunKey ,A.RunDate ,B.Item ,B.Value from @YourTable A cross apply (values ('Address' ,cast(A.[Address] as varchar(max))) ,('Salary' ,cast(A.[Salary] as varchar(max))) ,('PromotionDate',cast(A.[PromotionDate] as varchar(max))) ) B (Item,Value) ) select a.AccountNo ,a.RunKey ,a.RunDate ,stuff ((select (',' + Item) from ct b where b.AccountNo=a.AccountNo and b.RunKey=a.RunKey and b.RunDate=a.RunDate for xml path(''), type ).value('.', 'nvarchar(MAX)'),1,1,'') Item ,stuff ((select (',' + Value) from ct b where b.AccountNo=a.AccountNo and b.RunKey=a.RunKey and b.RunDate=a.RunDate for xml path(''), type ).value('.', 'nvarchar(MAX)'),1,1,'') Value from ct a group by a.AccountNo, a.RunKey, a.RunDate
db<>在這裡擺弄