如何使用UNPIVOT或UNION ALL实现多列逆透视并生成序号？

原表格数据：

Name    Status 1    Action 1    Status 2    Action 2    Status 3    Action 3
AAA     Started     S           Pended      P           Closed      C
BBB     Started     S           Pended      P           Closed      C
CCC     Started     S           Closed      C

需要生成的目标输出：

Name    Status   Action Seq No
AAA     Started   S      1
AAA     Pended    P      2
AAA     Closed    C      3
BBB     Started   S      1
BBB     Pended    P      2
BBB     Closed    C      3
CCC     Started   S      1
CCC     Closed    C      2

尝试使用UNPIVOT无法实现该需求，特此寻求解决方案。

解决方案 #

方法1：使用UNION ALL拆分分组 #

通过UNION ALL将每组Status和Action拆分为独立行，同时标记分组顺序，最后生成序号：

SELECT 
    Name,
    Status,
    Action,
    ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Seq) AS [Seq No]
FROM (
    -- 第一组状态动作
    SELECT Name, [Status 1] AS Status, [Action 1] AS Action, 1 AS Seq
    FROM YourTable
    WHERE [Status 1] IS NOT NULL
    UNION ALL
    -- 第二组状态动作
    SELECT Name, [Status 2] AS Status, [Action 2] AS Action, 2 AS Seq
    FROM YourTable
    WHERE [Status 2] IS NOT NULL
    UNION ALL
    -- 第三组状态动作
    SELECT Name, [Status 3] AS Status, [Action 3] AS Action, 3 AS Seq
    FROM YourTable
    WHERE [Status 3] IS NOT NULL
) AS UnpivotedData
ORDER BY Name, Seq;

方法2：使用CROSS APPLY + VALUES（更简洁） #

适用于SQL Server等支持CROSS APPLY的数据库，直接将单行多列转换为多行：

SELECT 
    t.Name,
    v.Status,
    v.Action,
    ROW_NUMBER() OVER (PARTITION BY t.Name ORDER BY v.Seq) AS [Seq No]
FROM YourTable t
CROSS APPLY (
    VALUES 
        (1, [Status 1], [Action 1]),
        (2, [Status 2], [Action 2]),
        (3, [Status 3], [Action 3])
) v(Seq, Status, Action)
WHERE v.Status IS NOT NULL
ORDER BY t.Name, v.Seq;

说明 #

两种方法都会自动过滤掉像CCC那样缺失的分组数据，并且通过ROW_NUMBER()按Name分组、分组顺序排序，生成连续的序号。相比UNPIVOT，这种方式更灵活，能直接控制分组的顺序和空值过滤逻辑。

内容的提问来源于stack exchange，提问作者SuperAmitbond