MS SQL Server 2017实现列转行：Excel导入表的列转行

原始表结构与数据 #

从Excel导入至MS SQL Server 2017的表，列名分别为[13]、[17]、[18]、[19]，示例数据如下：

目标表结构 #

需要将上述表转换为列转行后的结构：

实现SQL代码 #

方法1：使用UNION ALL拼接结果 #

假设导入后的表名为ImportedTable，执行以下SQL：

WITH DateMapping AS (
    -- 提取第一行的日期值
    SELECT 
        [17] AS Date1,
        [18] AS Date2,
        [19] AS Date3
    FROM ImportedTable
    WHERE [13] IS NULL OR [13] = ''
),
IndicatorData AS (
    -- 提取指标及对应日期的数值
    SELECT 
        [13] AS Indicator,
        [17] AS Value1,
        [18] AS Value2,
        [19] AS Value3
    FROM ImportedTable
    WHERE [13] IS NOT NULL AND [13] != ''
)
-- 拼接每个指标的三行数据
SELECT 
    id.Indicator,
    dm.Date1 AS Date,
    id.Value1 AS Value
FROM IndicatorData id, DateMapping dm
UNION ALL
SELECT 
    id.Indicator,
    dm.Date2 AS Date,
    id.Value2 AS Value
FROM IndicatorData id, DateMapping dm
UNION ALL
SELECT 
    id.Indicator,
    dm.Date3 AS Date,
    id.Value3 AS Value
FROM IndicatorData id, DateMapping dm
ORDER BY Indicator, Date;

方法2：使用UNPIVOT关联日期映射 #

这个方法更灵活，若后续新增日期列（如[20]），只需修改两处的列名即可：

WITH DateMapping AS (
    -- 将日期行转换为键值对，匹配后续UNPIVOT的列名
    SELECT 'Date1' AS ColName, [17] AS DateVal UNION ALL
    SELECT 'Date2' AS ColName, [18] AS DateVal UNION ALL
    SELECT 'Date3' AS ColName, [19] AS DateVal
    FROM ImportedTable
    WHERE [13] IS NULL OR [13] = ''
),
IndicatorUnpivot AS (
    -- 将指标的多列数值转为行
    SELECT 
        [13] AS Indicator,
        'Date' + RIGHT(ColName,1) AS ColName, -- 生成与DateMapping匹配的键名
        Value
    FROM ImportedTable
    WHERE [13] IS NOT NULL AND [13] != ''
    UNPIVOT (
        Value FOR ColName IN ([17], [18], [19])
    ) AS unpvt
)
-- 关联日期与指标数值
SELECT 
    iu.Indicator,
    dm.DateVal AS Date,
    iu.Value
FROM IndicatorUnpivot iu
JOIN DateMapping dm ON iu.ColName = dm.ColName
ORDER BY iu.Indicator, dm.DateVal;

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