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

Question

I have a table History with columns as 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 a query

SELECT
  *
FROM History
WHERE DeptId = 'D34'
ORDER BY Updateddate

The result of this query gives me the above 3 rows. Now out of these rows I want the name and value of columns which have different values in consecutive rows.

Something like:

HistoryId-----Column-----Value
2             User       B24
3             Tag        def

Is there a way to do this?

Answer 1

If there are no null values for tag or user, then one way you could achieve this is by using a combination of LAG() and a CROSS APPLY to check whether any values are different.

SELECT H.HistoryID, C.Col, C.Val
FROM (
    SELECT *, PrevUser = LAG([User]) OVER (ORDER BY HistoryID), PrevTag = LAG([Tag]) OVER (ORDER BY HistoryID)
    FROM [History] AS H
) AS H
CROSS APPLY (
    VALUES
        ('User', CASE WHEN PrevUser != [User] THEN [User] END),
        ('Tag', CASE WHEN PrevTag != Tag THEN Tag END)
) AS C(Col, Val)
WHERE C.Val IS NOT NULL;

If there are null values, it gets a bit more complicated, but the basic idea would be the same, you'd just have to add in rules to check for null values (and ignore the first row).

EDIT: If you needed to check for null values too, one way to do it would be like the 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
FROM (
    SELECT *, RN = ROW_NUMBER() OVER (ORDER BY HistoryID), PrevUser = LAG([User]) OVER (ORDER BY HistoryID), PrevTag = LAG([Tag]) OVER (ORDER BY HistoryID)
    FROM @History AS H
) AS H
CROSS APPLY (
    VALUES
        ('User', CASE WHEN RN != 1 AND (PrevUser != [User] OR (PrevUser IS NULL AND [User] IS NOT NULL) OR (PrevUser IS NOT NULL AND [User] IS NULL)) THEN [User] END, CASE WHEN RN != 1 AND (PrevUser != [User] OR (PrevUser IS NULL AND [User] IS NOT NULL) OR (PrevUser IS NOT NULL AND [User] IS NULL)) THEN 1 END),
        ('Tag', CASE WHEN RN != 1 AND (PrevTag != Tag OR (PrevTag IS NULL AND Tag IS NOT NULL) OR (PrevTag IS NOT NULL AND Tag IS NULL)) THEN Tag END, CASE WHEN RN != 1 AND (PrevTag != Tag OR (PrevTag IS NULL AND Tag IS NOT NULL) OR (PrevTag IS NOT NULL AND Tag IS NULL)) THEN 1 END)
) AS C(Col, Val, Chk)
WHERE C.Chk = 1;