sql - How to get the name and values of columns which have different values in the same table's consecutive rows -

i have table history columns follows:

historyid---user---tag---updateddate----deptid 1           b12    abc   10-08-2017      d34 2           b24    abc   11-08-2017      d34 3           b24    def   12-08-2017      d34 

i have query

select   * history deptid = 'd34' order updateddate 

the result of query gives me above 3 rows. out of these rows want name , value of columns have different values in consecutive rows.

something like:

historyid-----column-----value 2             user       b24 3             tag        def 

is there way this?

if there no null values tag or user, 1 way achieve using combination of lag() , cross apply check whether values different.

select h.historyid, c.col, c.val (     select *, prevuser = lag([user]) on (order historyid), prevtag = lag([tag]) on (order historyid)     [history] h ) h cross apply (     values         ('user', case when prevuser != [user] [user] end),         ('tag', case when prevtag != tag tag end) ) c(col, val) c.val not null; 

if there null values, gets bit more complicated, basic idea same, you'd have add in rules check null values (and ignore first row).

edit: if needed check null values too, 1 way following...

declare @history table (historyid int, [user] char(3), [tag] char(3)); insert @history values      (1,'b12','abc'),     (2,'b24','abc'),     (3,'b24','def'),     (4,null,'def'),     (5,'a24',null),     (6,null,null),     (7,'123','456');  select h.historyid, c.col, c.val (     select *, rn = row_number() on (order historyid), prevuser = lag([user]) on (order historyid), prevtag = lag([tag]) on (order historyid)     @history h ) h cross apply (     values         ('user', case when rn != 1 , (prevuser != [user] or (prevuser null , [user] not null) or (prevuser not null , [user] null)) [user] end, case when rn != 1 , (prevuser != [user] or (prevuser null , [user] not null) or (prevuser not null , [user] null)) 1 end),         ('tag', case when rn != 1 , (prevtag != tag or (prevtag null , tag not null) or (prevtag not null , tag null)) tag end, case when rn != 1 , (prevtag != tag or (prevtag null , tag not null) or (prevtag not null , tag null)) 1 end) ) c(col, val, chk) c.chk = 1;