SQL query to find difference in text between different rows, Sql Server

SQL query to find difference in text between different rows

declare @Changes table(
  ID int,
  [Date] datetime,
  Author nvarchar(30),
  Data1 nvarchar(30),
  Data2 nvarchar(30),
  Data3 nvarchar(30),
  Data4 nvarchar(30),
  Data5 nvarchar(30),
  Data6 nvarchar(30),
  Data7 nvarchar(30),
  Data8 nvarchar(30),
  Data9 nvarchar(30)
);

with
  d as(
    select row_number() over(order by(select 0)) DataId,Data
    from(values('Foo'),('Goo'),('Hoo'),('Bar'),('Car')) d(Data)
  ),
  a as(
    select top(6*9)
      row_number() over(order by(select 0)) rn,DataId
    from (select top(6*9)
            abs(checksum(newid()))%3+iif(checksum(newid())%2=0,1,3) DataId
          from master..spt_values) t
  ),
  b as(
    select a.rn%6+1 ID,d.Data from a join d on a.DataId=d.DataId
  ),
  c as(
    select top(6)
      row_number() over(order by(select 0)) AuthId,*
    from (
      select top(6)
        iif(checksum(newid())%2=0,'John Smith','Bill Plith') Author,
        dateadd(ss,abs(checksum(newid()))%60,getdate()) [Date]
      from master..spt_values
    ) t
  ),
  e as(
    select b.ID,dateadd(mi,b.ID,c.[Date]) [Date],c.Author,b.Data,
      'Data'+ltrim(str(row_number() over(partition by b.ID order by (select 0))%9+1)) DataNm
    from b join c on b.ID=c.AuthId
  ),
  i as(
    select *
    from e
    pivot (
      min(Data)
      For DataNm in (Data1,Data2,Data3,Data4,Data5,Data6,Data7,Data8,Data9)
    ) p
  )
insert into @Changes(ID,[Date],Author,Data1,Data2,Data3,Data4,Data5,Data6,Data7,Data8,Data9)
  select ID,[Date],Author,Data1,Data2,Data3,Data4,Data5,Data6,Data7,Data8,Data9 from i;

select * from @Changes order by [Date];

with
  unp as(
    select *
    from @Changes
    unpivot (
      Data For DataNam in (Data1,Data2,Data3,Data4,Data5,Data6,Data7,Data8,Data9)
    ) u
  ),
  a as(
    select *,
      lag(Data,1,Data) over(partition by DataNam order by [Date]) PrevData
    from unp
  )
select [Date], Author,DataNam+' was changed to '+Data Changes
from a
where Data!=PrevData
order by DataNam,[Date];


run \| edit \| history \| help	0