SQL中利用列名计算日期值，实现宽表转日期结构化表

我有一张结构特殊的表，针对唯一ID序列，设有28-31个对应每月日期的列（列名是日期数字，如1、2...）。希望将其转换为包含实际日期值的易用格式，同时要求实现方法能灵活适配不同月份的列数（对应不同天数），且兼顾性能。

原表结构示例 #

DECLARE @Month VARCHAR(3) 
SET @Month = 'NOV'

目标格式 #

方法1：动态SQL + UNPIVOT（灵活适配任意月份天数） #

自动识别表中所有日期列（列名为数字的列），生成对应UNPIVOT语句，同时将列名转换为实际日期。假设表名为MonthlyData，年份固定为2022（需动态年份可添加@Year参数）：

DECLARE @Month VARCHAR(3) = 'NOV';
DECLARE @Year INT = 2022;
DECLARE @DateColumns NVARCHAR(MAX);
DECLARE @SQL NVARCHAR(MAX);

-- 获取所有日期列（排除ID、Status，仅保留数字列名）
SELECT @DateColumns = STRING_AGG(QUOTENAME(COLUMN_NAME), ', ')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'MonthlyData'
  AND COLUMN_NAME NOT IN ('ID', 'Status')
  AND ISNUMERIC(COLUMN_NAME) = 1;

-- 生成动态UNPIVOT执行语句
SET @SQL = N'
SELECT 
  ID,
  Status,
  CONVERT(DATE, CAST(' + CAST(@Year AS NVARCHAR) + ' AS VARCHAR) + ''-'' + @Month + ''-'' + DayNum) AS Date,
  Value
FROM (
  SELECT 
    ID,
    Status,
    ' + @DateColumns + '
  FROM MonthlyData
) AS SourceTable
UNPIVOT (
  Value FOR DayNum IN (' + @DateColumns + ')
) AS UnpivotTable;';

-- 执行动态SQL
EXEC sp_executesql @SQL, N'@Month VARCHAR(3)', @Month;

说明

• 自动适配28-31天的不同月份，无需手动指定列名。
• 用STRING_AGG（SQL Server 2017+）拼接列名，低版本可替换为STUFF+FOR XML PATH的方式。
• UNPIVOT是SQL原生列转行操作，效率优于手动UNION ALL。

方法2：预定义所有日期列 + 条件过滤（性能优先场景） #

若表结构固定包含1-31列（部分月份无对应日期的列值为NULL），可使用静态UNPIVOT配合日期有效性过滤，避免动态SQL开销：

DECLARE @Month VARCHAR(3) = 'NOV';
DECLARE @Year INT = 2022;

SELECT 
  ID,
  Status,
  CONVERT(DATE, CAST(@Year AS VARCHAR) + '-' + @Month + '-' + DayNum) AS Date,
  Value
FROM (
  SELECT 
    ID,
    Status,
    [1], [2], [3], [4], [5], [6], [7], [8], [9], [10],
    [11], [12], [13], [14], [15], [16], [17], [18], [19], [20],
    [21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31]
  FROM MonthlyData
) AS SourceTable
UNPIVOT (
  Value FOR DayNum IN (
    [1], [2], [3], [4], [5], [6], [7], [8], [9], [10],
    [11], [12], [13], [14], [15], [16], [17], [18], [19], [20],
    [21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31]
  )
) AS UnpivotTable
-- 过滤当前月份不存在的无效日期
WHERE CONVERT(DATE, CAST(@Year AS VARCHAR) + '-' + @Month + '-' + DayNum) IS NOT NULL;

说明

• 静态SQL的执行计划可缓存，性能比动态SQL更稳定。
• 通过WHERE条件自动过滤无效日期（如2月30日转换后为NULL，会被剔除）。
• 缺点是需预先列出1-31所有列，表结构变更时需同步修改SQL。

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