如何高效实现SQL列转行（替代多Union语句）？

问题背景 #

现有如下结构化数据表：

需要转换为字段名、字段值分离的列转行格式，示例输出如下：

当前采用UNION ALL手动拼接实现，但待转换列数量多，维护成本极高。需求是：无需手动更新每个UNION语句，通过遍历列的方式实现高效转换，同时减少输出行数、便于跨指标对比完成率。

解决方案 #

1. 原生UNPIVOT语法（SQL Server、Oracle） #

如果使用支持UNPIVOT的数据库，可直接用该语法一次性完成列转行，无需多个UNION ALL：

SELECT 
    Company,
    Completed,
    FieldName AS "Field Name",
    FieldValue AS "Field Value",
    -- Total按实际业务逻辑计算，示例为分组统计数
    COUNT(*) OVER (PARTITION BY Company, Completed, FieldName, FieldValue) AS Total
FROM 
    YourTableName
UNPIVOT (
    FieldValue FOR FieldName IN (DayofWeek, Hour, Country, Year)
) AS UnpivotTable
-- 可按需添加WHERE过滤条件
ORDER BY 
    Company, Completed, FieldName, FieldValue;

2. LATERAL JOIN/交叉连接（PostgreSQL、MySQL 8.0+） #

对于支持横向连接的数据库，可通过该方式实现列转行：

PostgreSQL示例：

SELECT 
    t.Company,
    t.Completed,
    u.field_name AS "Field Name",
    u.field_value AS "Field Value",
    COUNT(*) OVER (PARTITION BY t.Company, t.Completed, u.field_name, u.field_value) AS Total
FROM 
    YourTableName t
LATERAL (
    VALUES
        ('DayofWeek', t.DayofWeek::TEXT),
        ('Hour', t.Hour::TEXT),
        ('Country', t.Country::TEXT),
        ('Year', t.Year::TEXT)
) u(field_name, field_value)
ORDER BY 
    t.Company, t.Completed, u.field_name, u.field_value;

MySQL 8.0+示例：

SELECT 
    t.Company,
    t.Completed,
    j.field_name AS "Field Name",
    j.field_value AS "Field Value",
    COUNT(*) OVER (PARTITION BY t.Company, t.Completed, j.field_name, j.field_value) AS Total
FROM 
    YourTableName t
JOIN JSON_TABLE(
    JSON_ARRAY(
        JSON_OBJECT('field_name', 'DayofWeek', 'field_value', t.DayofWeek),
        JSON_OBJECT('field_name', 'Hour', 'field_value', t.Hour),
        JSON_OBJECT('field_name', 'Country', 'field_value', t.Country),
        JSON_OBJECT('field_name', 'Year', 'field_value', t.Year)
    ),
    '$[*]' COLUMNS(
        field_name VARCHAR(50) PATH '$.field_name',
        field_value VARCHAR(50) PATH '$.field_value'
    )
) j
ORDER BY 
    t.Company, t.Completed, j.field_name, j.field_value;

3. 动态SQL（通用方案） #

如果待转换列频繁变动，可通过动态SQL自动识别列名，彻底避免手动维护：

SQL Server动态SQL示例：

DECLARE @cols NVARCHAR(MAX);
DECLARE @query NVARCHAR(MAX);

-- 自动获取需转置的列（排除不需要转的Company、Completed）
SELECT @cols = STRING_AGG(QUOTENAME(column_name), ', ')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'YourTableName'
  AND column_name NOT IN ('Company', 'Completed');

-- 生成UNPIVOT查询语句
SET @query = N'
SELECT 
    Company,
    Completed,
    FieldName AS "Field Name",
    FieldValue AS "Field Value",
    COUNT(*) OVER (PARTITION BY Company, Completed, FieldName, FieldValue) AS Total
FROM 
    YourTableName
UNPIVOT (
    FieldValue FOR FieldName IN (' + @cols + N')
) AS UnpivotTable
ORDER BY 
    Company, Completed, FieldName, FieldValue;';

-- 执行动态SQL
EXEC sp_executesql @query;

PostgreSQL动态SQL示例：

DO $$
DECLARE
    cols TEXT;
    query TEXT;
BEGIN
    -- 获取需转置的列名
    SELECT string_agg(format('(''%s'', %I::TEXT)', column_name, column_name), ', ')
    INTO cols
    FROM information_schema.columns
    WHERE table_name = 'yourtablename'
      AND column_name NOT IN ('company', 'completed');

    -- 生成查询语句
    query := format('
        SELECT 
            t.company,
            t.completed,
            u.field_name AS "Field Name",
            u.field_value AS "Field Value",
            COUNT(*) OVER (PARTITION BY t.company, t.completed, u.field_name, u.field_value) AS Total
        FROM 
            yourtablename t
        LATERAL (
            VALUES %s
        ) u(field_name, field_value)
        ORDER BY 
            t.company, t.completed, u.field_name, u.field_value;', cols);

    -- 执行查询
    EXECUTE query;
END $$;

说明 #

• 示例中Total字段用窗口函数统计分组数量，可根据实际业务需求替换为求和、预设值等逻辑。
• 动态SQL方案会自动识别目标表中除Company、Completed外的所有列，后续新增/删除列无需修改代码，彻底解决手动维护问题。

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