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

SQL query to find difference in text between different rows

Language: Layout:

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

View schema
Execution time: 0 sec, rows selected: 39, rows affected: 6, absolute service time: 0,17 sec

	ID	Date	Author	Data1	Data2	Data3	Data4	Data5	Data6	Data7	Data8	Data9
1	1	31.05.2018 10:01:39	John Smith	Goo	Bar	Foo	Foo	Bar	Goo	Foo	Hoo	Bar
2	2	31.05.2018 10:02:01	John Smith	Bar	Hoo	Car	Foo	Bar	Hoo	Hoo	Bar	Hoo
3	3	31.05.2018 10:03:45	Bill Plith	Hoo	Hoo	Goo	Hoo	Foo	Bar	Bar	Goo	Bar
4	4	31.05.2018 10:04:09	John Smith	Hoo	Car	Car	Hoo	Hoo	Bar	Foo	Foo	Foo
5	5	31.05.2018 10:04:54	Bill Plith	Hoo	Bar	Hoo	Goo	Bar	Goo	Hoo	Hoo	Goo
6	6	31.05.2018 10:06:10	John Smith	Foo	Goo	Hoo	Hoo	Bar	Bar	Hoo	Hoo	Goo

	Date	Author	Changes
1	31.05.2018 10:02:01	John Smith	Data1 was changed to Bar
2	31.05.2018 10:03:45	Bill Plith	Data1 was changed to Hoo
3	31.05.2018 10:06:10	John Smith	Data1 was changed to Foo
4	31.05.2018 10:02:01	John Smith	Data2 was changed to Hoo
5	31.05.2018 10:04:09	John Smith	Data2 was changed to Car
6	31.05.2018 10:04:54	Bill Plith	Data2 was changed to Bar
7	31.05.2018 10:06:10	John Smith	Data2 was changed to Goo
8	31.05.2018 10:02:01	John Smith	Data3 was changed to Car
9	31.05.2018 10:03:45	Bill Plith	Data3 was changed to Goo
10	31.05.2018 10:04:09	John Smith	Data3 was changed to Car
11	31.05.2018 10:04:54	Bill Plith	Data3 was changed to Hoo
12	31.05.2018 10:03:45	Bill Plith	Data4 was changed to Hoo
13	31.05.2018 10:04:54	Bill Plith	Data4 was changed to Goo
14	31.05.2018 10:06:10	John Smith	Data4 was changed to Hoo
15	31.05.2018 10:03:45	Bill Plith	Data5 was changed to Foo
16	31.05.2018 10:04:09	John Smith	Data5 was changed to Hoo
17	31.05.2018 10:04:54	Bill Plith	Data5 was changed to Bar
18	31.05.2018 10:02:01	John Smith	Data6 was changed to Hoo
19	31.05.2018 10:03:45	Bill Plith	Data6 was changed to Bar
20	31.05.2018 10:04:54	Bill Plith	Data6 was changed to Goo
21	31.05.2018 10:06:10	John Smith	Data6 was changed to Bar
22	31.05.2018 10:02:01	John Smith	Data7 was changed to Hoo
23	31.05.2018 10:03:45	Bill Plith	Data7 was changed to Bar
24	31.05.2018 10:04:09	John Smith	Data7 was changed to Foo
25	31.05.2018 10:04:54	Bill Plith	Data7 was changed to Hoo
26	31.05.2018 10:02:01	John Smith	Data8 was changed to Bar
27	31.05.2018 10:03:45	Bill Plith	Data8 was changed to Goo
28	31.05.2018 10:04:09	John Smith	Data8 was changed to Foo
29	31.05.2018 10:04:54	Bill Plith	Data8 was changed to Hoo
30	31.05.2018 10:02:01	John Smith	Data9 was changed to Hoo
31	31.05.2018 10:03:45	Bill Plith	Data9 was changed to Bar
32	31.05.2018 10:04:09	John Smith	Data9 was changed to Foo
33	31.05.2018 10:04:54	Bill Plith	Data9 was changed to Goo