如何用SQL将周数值字段转换为行？无需编写52个Case语句

要将包含Code、Year和52个周值列（Wk1Val至Wk52Val）的宽表转换为Code、Year、Week、Value的窄表结构，完全不需要逐个编写52个CASE语句，核心是利用数据库的列转行能力，以下是主流数据库的实现方案：

SQL Server 方案 #

静态列转行（UNPIVOT） #

直接使用UNPIVOT关键字，虽需列出所有周值列，但无需CASE：

SELECT 
    Code, 
    Year, 
    -- 从列名中提取周数（如Wk1Val转为1）
    CAST(REPLACE(REPLACE(Week, 'Wk', ''), 'Val', '') AS INT) AS Week,
    Value
FROM 原表名
UNPIVOT (
    Value FOR Week IN (Wk1Val, Wk2Val, Wk3Val, ..., Wk52Val)
) AS unpvt_result

动态生成列列表（避免手动写52个列） #

通过系统表自动获取所有周值列，动态拼接SQL：

DECLARE @column_list NVARCHAR(MAX);
-- 自动提取所有WkXVal格式的列名
SELECT @column_list = STRING_AGG(QUOTENAME(name), ', ')
FROM sys.columns
WHERE object_id = OBJECT_ID('原表名') AND name LIKE 'Wk%Val';

-- 拼接并执行动态SQL
DECLARE @sql NVARCHAR(MAX) = N'
SELECT 
    Code, 
    Year, 
    CAST(REPLACE(REPLACE(Week, ''Wk'', ''''), ''Val'', '''') AS INT) AS Week,
    Value
FROM 原表名
UNPIVOT (
    Value FOR Week IN (' + @column_list + ')
) AS unpvt_result';

EXEC sp_executesql @sql;

MySQL 方案 #

MySQL无原生UNPIVOT，可借助JSON函数+动态SQL实现：

-- 动态获取所有周值列
SET @column_list = (
    SELECT GROUP_CONCAT(CONCAT('`', column_name, '`')) 
    FROM information_schema.columns 
    WHERE table_name = '原表名' AND column_name LIKE 'Wk%Val'
);

-- 拼接SQL，用JSON_TABLE将列转为键值对
SET @sql = CONCAT('
SELECT 
    Code, 
    Year, 
    CAST(REPLACE(REPLACE(week_col, ''Wk'', ''''), ''Val'', '''') AS UNSIGNED) AS Week,
    value
FROM 原表名
JOIN JSON_TABLE(
    JSON_OBJECT(', @column_list, '),
    ''$.*'' COLUMNS (
        week_col VARCHAR(10) PATH ''$.key'',
        value INT PATH ''$.value''
    )
) AS jt');

-- 执行动态SQL
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

PostgreSQL 方案 #

利用JSONB和UNNEST自动展开列：

SELECT 
    Code, 
    Year, 
    CAST(REPLACE(REPLACE(week_col, 'Wk', ''), 'Val', '') AS INT) AS Week,
    value::INT AS Value
FROM 原表名,
     -- 将除Code、Year外的列转为键值对数组并展开
     UNNEST(ARRAY(SELECT jsonb_each_text(to_jsonb(原表名) - 'Code' - 'Year'))) AS j(week_col, value);

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