SQL Server动态逆透视（Dynamic UnPivot）实现求助：处理L1-L5动态列

原始输入数据 #

L1  L2  L3  Year         ID
----------------------------------
0    0   1    2019        1
1    0   0    2020        2
------------------------------------

需求说明 #

L1、L2、L3为动态列，最多可扩展至L5，需要实现动态逆透视，将这些列转换为行结构，兼容L1到L5的所有可能组合。

预期输出 #

ColumnName  ColumnValue  Year  ID
----------------------------------
L1          0            2019  1
L2          0            2019  1
L3          1            2019  1
L1          1            2020  2
L2          0            2020  2
L3          0            2020  2

实现代码 #

以下是兼容L1-L5动态列的SQL Server动态逆透视脚本，只需替换实际表名即可：

DECLARE @colsUnpivot NVARCHAR(MAX),
        @query NVARCHAR(MAX)

-- 自动获取所有以L开头的列（匹配L1、L2...L5）
SELECT @colsUnpivot = STRING_AGG(QUOTENAME(name), ', ')
FROM sys.columns
WHERE object_id = OBJECT_ID('你的表名') -- 替换为实际表名
  AND name LIKE 'L[0-9]'

-- 构建动态逆透视语句
SET @query = N'
SELECT 
    ColumnName = REPLACE(REPLACE(pvt.ColumnName, ''['', ''''), '']'', ''''),
    ColumnValue = pvt.ColumnValue,
    Year,
    ID
FROM 你的表名
UNPIVOT
(
    ColumnValue FOR ColumnName IN (' + @colsUnpivot + ')
) AS pvt
ORDER BY Year, ID, ColumnName'

-- 执行动态SQL
EXEC sp_executesql @query

补充说明 #

1. 动态列适配：通过sys.columns系统视图自动筛选符合命名规则的列，无需手动指定L1-L5，后续列扩展时无需修改脚本
2. 低版本兼容：STRING_AGG适用于SQL Server 2017及以上版本；若使用更低版本，可替换为FOR XML PATH拼接列名：

   SELECT @colsUnpivot = STUFF((SELECT ', ' + QUOTENAME(name)
                               FROM sys.columns
                               WHERE object_id = OBJECT_ID('你的表名')
                                 AND name LIKE 'L[0-9]'
                               ORDER BY name
                               FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
   

3. 排序逻辑：最终结果按Year、ID、ColumnName排序，保证输出顺序清晰

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