0

I have a table that gets inserted into every night that is a snapshot of the data. At any point in time, the data might change in the columns (AccountNo stays the same, RunKey is incremented by 1, and RunDate increments by 1 day; all other columns can change ad-hoc). Below is a sample of how the data looks:

|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 |

There will be a new record for each column change. So if multiple columns changed, each change would be documented in a new line. I was able to dynamically insert only changed columns into the new table.

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

enter image description here

This is where I need help, I don't know how show all the changes for the same date, accountNo and RunKey in one row instead of multiple rows. is this possible to achieve? sample of needed / similar result needed: enter image description here

3
  • It is not clear what is your desired result and what is the partition that you want to use. Can you add a desired result for the example that you provided, and if possible adding another Item, it seems AccountNo and RunKey can have duplicated values depending on it.
    – McNets
    Nov 15 '21 at 19:08
  • @McNets I updated the question to display a sample result? essentially i need that if AccountNo, RunKey and RunDate are the same then all Item and Value changes must be displayed int he same row instead of multiple rows
    – Jen
    Nov 15 '21 at 19:22
  • And what about PreValue and PreDate?, they aren't in your result.
    – McNets
    Nov 15 '21 at 19:28
0

If I correctly understand this is what you're looking for:

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
AccountNo RunKey RunDate Item Value
12345 2 2017-06-20 Address,Salary,PromotionDate 123 Main Street,60000,2017-01-15
12345 3 2017-06-21 Address,Salary,PromotionDate 123 Main Street,60000,2017-01-15
12345 4 2017-06-22 Address,Salary,PromotionDate 123 Main Street,65000,2017-06-21

db<>fiddle here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.