sql - 如何获取同一表连续行中具有不同值的列的名称和值

我有一个包含列的表 History 如下:

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

我有一个查询

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

这个查询的结果给了我上面的 3 行。
现在，在这些行中，我想要在连续行中具有不同值的列的名称和值。

就像是:

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

有没有办法做到这一点？

最佳答案

如果 tag 或 user 没有空值，那么实现此目的的一种方法是使用 LAG() 和 CROSS APPLY 的组合来检查是否有任何值不同。

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;

如果有空值，它会变得有点复杂，但基本思想是相同的，您只需要添加规则来检查空值(并忽略第一行)。

编辑: 如果您也需要检查空值，一种方法如下...

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;

关于sql - 如何获取同一表连续行中具有不同值的列的名称和值，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/45789725/