sql - Showing History of changes from a History table -

i have history table, created insert , update triggers. history row contains row on insert/update.

what being asked show changes each user through time. so, below have in form of history table, , then, created dummy expected results.

declare @mytable table (     id int not null identity(1,1),     userid int not null,     locationid int not null,     roleid int not null,     lastupdateuserid int not null,     lastupdatedate datetime not null )   insert @mytable (userid, locationid, roleid, lastupdateuserid, lastupdatedate) select 1, 1000, 1, 7, getdate()+1 union select 2, 1100, 5, 9, getdate()+2 union select 2, 1110, 5, 6, getdate()+3 union select 1, 1100, 3, 6, getdate()+4 union select 4, 1500, 5, 8, getdate()+5 union select 7, 1000, 8, 9, getdate()+6 union select 7, 1100, 9, 9, getdate()+7 union select 1, 1000, 3, 7, getdate()+8 union select 9, 1100, 5, 2, getdate()+9 union select 9, 1100, 6, 5, getdate()+10   select * @mytable order id  declare @expectedresult table (     changetype char(1), -- i=insert, u=update     userid int,     changedate datetime,     changedbyuser int,     fieldname varchar(20),     oldvalue int,     newvalue int )  insert @expectedresult (changetype, userid, changedate, changedbyuser, fieldname, oldvalue, newvalue) select 'i', 1, '2015-apr-30 09:56:28', 7, 'locationid', null, 1000 union -- row1 select 'i', 1, '2015-apr-30 09:56:28', 7, 'roleid', null, 1 union -- row1 select 'u', 1, '2015-apr-07 10:27:42', 7, 'roleid', 1, 3 union -- row 2 select 'u', 1, '2015-may-03 10:27:42', 6, 'locationid', 1000, 1100 union -- row 3 select 'i', 2, '2015-may-01 10:27:42', 9, 'roleid', null, 5 union -- row5 select 'i', 2, '2015-may-01 10:27:42', 9, 'locationid', null, 1100 -- row5  select * @expectedresult 

@mytable has data @ moment. trying transform @expectedresults. we're reporting on changes roleid , locationid. on each change, needs have separate line each column. so, on insert, have 2 lines (as monitor changes both fields). when 1 column updated, needs represented 1 'u' line. if both fields updated in same update statement, result in 2 update rows in @expected.

i started cursor, hoping there more efficient way achieve this.

to roleid , locationid on separate rows can use simple union all.

and combine old , new values use row_number() window function, this:

;with t as(     select *,     row_number() over(partition userid order lastupdatedate) rn     @mytable ), ( select userid, 'locationid' fieldname, locationid value, lastupdateuserid, lastupdatedate, rn t union select userid, 'roleid' fieldname, roleid value, lastupdateuserid, lastupdatedate, rn t ) select case when a2.userid null 'i' else 'u' end changetype, a1.userid, a1.lastupdatedate, a1.lastupdateuserid, a1.fieldname, a1.value newvalue, a2.value oldvalue a1 left join a2 on a1.userid = a2.userid , a1.fieldname = a2.fieldname , a1.rn = a2.rn+1 order 2,3,5 

the a1 alias in query above contains "new values", a2 contains "old values". when use real data need partition fieldname (and perhaps table name) , join them

the result:

changetype userid      lastupdatedate          lastupdateuserid fieldname  newvalue    oldvalue ---------- ----------- ----------------------- ---------------- ---------- ----------- -----------          1           2015-04-30 12:20:59.183 7                locationid 1000        null          1           2015-04-30 12:20:59.183 7                roleid     1           null u          1           2015-05-03 12:20:59.183 6                locationid 1100        1000 u          1           2015-05-03 12:20:59.183 6                roleid     3           1 u          1           2015-05-07 12:20:59.183 7                locationid 1000        1100 u          1           2015-05-07 12:20:59.183 7                roleid     3           3          2           2015-05-01 12:20:59.183 9                locationid 1100        null          2           2015-05-01 12:20:59.183 9                roleid     5           null u          2           2015-05-02 12:20:59.183 6                locationid 1110        1100 u          2           2015-05-02 12:20:59.183 6                roleid     5           5          4           2015-05-04 12:20:59.183 8                locationid 1500        null          4           2015-05-04 12:20:59.183 8                roleid     5           null          7           2015-05-05 12:20:59.183 9                locationid 1000        null          7           2015-05-05 12:20:59.183 9                roleid     8           null u          7           2015-05-06 12:20:59.183 9                locationid 1100        1000 u          7           2015-05-06 12:20:59.183 9                roleid     9           8          9           2015-05-08 12:20:59.183 2                locationid 1100        null          9           2015-05-08 12:20:59.183 2                roleid     5           null u          9           2015-05-09 12:20:59.183 5                locationid 1100        1100 u          9           2015-05-09 12:20:59.183 5                roleid     6           5  (20 row(s) affected)