How to group / display same date data in one row

ctesql serversql-server-2012t-sql

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

Best Answer

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

Related Question