如何将含多诊断码的行逆透视并获取DX_Order列？

你已经用UNPIVOT完成了宽表转窄表的核心操作，现在只需要小改动就能加上对应的诊断码序号，这里有两种实用方法可以实现：

方法一：基于现有UNPIVOT查询快速添加序号 #

UNPIVOT生成的DXs字段其实就是原表的列名（比如DX01、DX02），我们可以直接从这个列名里提取数字部分作为DX_Order：

SELECT 
    ID, 
    Name,
    CAST(RIGHT(DXs, 2) AS INT) AS DX_Order, -- 从列名提取序号，自动去掉前导零
    DX
FROM (
 SELECT ID, Name, DX01, DX02, DX03, ..., DX25
 FROM dbo.Table
 ) AS cp
UNPIVOT (
 DX FOR DXs IN (DX01, DX02, DX03, ...., DX25)
 ) AS up;

注：因为你的列名是DX01到DX25的规则，RIGHT(DXs,2)能精准提取末尾两位数字，转成INT后就得到1、2这类序号。而且你提到的UNPIVOT自动忽略NULL值的特性完全生效，不需要额外加过滤条件。

方法二：用CROSS APPLY + VALUES实现更直观的逆透视 #

如果觉得处理字符串不够稳妥，也可以用CROSS APPLY配合VALUES子句手动关联每个诊断码列和对应的序号，这种写法更清晰，后续维护也更方便：

SELECT 
    ID, 
    Name,
    DX_Order,
    DX
FROM dbo.Table
CROSS APPLY (
    VALUES
        (1, DX01),
        (2, DX02),
        (3, DX03),
        -- ... 依次补充到(25, DX25)
        (25, DX25)
) AS ca(DX_Order, DX)
WHERE DX IS NOT NULL; -- 手动过滤NULL值

这种方法不依赖列名的命名规则，直接指定序号和对应列的映射，哪怕后续列名有调整，修改起来也更灵活。

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