Sql-Server

如何在一行中分組/顯示相同的日期數據?

  • November 15, 2021

我有一個表格,每晚都會插入一個表格,它是數據的快照。在任何時間點,列中的數據都可能發生變化(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<>在這裡擺弄

引用自:https://dba.stackexchange.com/questions/302648