如何在SQL中将列中值拆分至独立列并对应文本置于下方

针对你需要将一列中的值拆分到独立列、让对应文本置于列下方的需求，以下是不同主流数据库的实现方案：

先明确数据场景（以典型结构为例） #

假设你的原始表是键值对行存储，结构和数据如下：

-- 示例表结构
CREATE TABLE original_data (
    id INT,          -- 分组标识（比如每条记录的唯一ID）
    field_name VARCHAR(50), -- 字段名（比如"姓名""年龄"）
    field_value TEXT        -- 字段对应的值
);

-- 示例数据
INSERT INTO original_data VALUES
(1, '姓名', '张三'),
(1, '年龄', '30'),
(1, '职业', '程序员'),
(2, '姓名', '李四'),
(2, '年龄', '28'),
(2, '职业', '设计师');

目标是转成列式结构：每个字段名作为列名，对应的值填充到列下方。

MySQL 实现方法 #

固定字段名（静态转列） #

用CASE WHEN配合聚合函数直接指定列：

SELECT
    id,
    MAX(CASE WHEN field_name = '姓名' THEN field_value END) AS '姓名',
    MAX(CASE WHEN field_name = '年龄' THEN field_value END) AS '年龄',
    MAX(CASE WHEN field_name = '职业' THEN field_value END) AS '职业'
FROM original_data
GROUP BY id;

字段名不固定（动态转列） #

如果字段名是动态变化的，用动态SQL自动生成查询语句：

SET @sql = NULL;
SELECT
    GROUP_CONCAT(DISTINCT
        CONCAT(
            'MAX(CASE WHEN field_name = ''',
            field_name,
            ''' THEN field_value END) AS ''',
            field_name,
            ''''
        )
    ) INTO @sql
FROM original_data;

SET @sql = CONCAT('SELECT id, ', @sql, ' FROM original_data GROUP BY id');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SQL Server 实现方法 #

固定字段名（静态转列） #

用PIVOT语法快速转列：

SELECT id, [姓名], [年龄], [职业]
FROM (
    SELECT id, field_name, field_value
    FROM original_data
) AS src
PIVOT (
    MAX(field_value)
    FOR field_name IN ([姓名], [年龄], [职业])
) AS pvt;

字段名不固定（动态转列） #

通过拼接动态SQL实现自适应字段：

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

-- 生成列名列表
SET @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(field_name)
                    FROM original_data
                    FOR XML PATH(''), TYPE
                    ).value('.', 'NVARCHAR(MAX)') 
                ,1,1,'');

-- 生成完整查询语句
SET @query = 'SELECT id, ' + @cols + ' from 
             (
                SELECT id, field_name, field_value
                FROM original_data
            ) x
            PIVOT 
            (
                MAX(field_value)
                FOR field_name IN (' + @cols + ')
            ) p ';

EXECUTE(@query);

PostgreSQL 实现方法 #

固定字段名（静态转列） #

需要先启用tablefunc扩展，再用crosstab函数转列：

-- 启用扩展（仅需执行一次）
CREATE EXTENSION IF NOT EXISTS tablefunc;

SELECT *
FROM crosstab(
    'SELECT id, field_name, field_value FROM original_data ORDER BY 1,2',
    'SELECT DISTINCT field_name FROM original_data ORDER BY 1'
) AS ct(id INT, 姓名 TEXT, 年龄 TEXT, 职业 TEXT);

字段名不固定（动态转列） #

用PL/pgSQL生成动态查询：

DO $$
DECLARE
    cols TEXT;
    query TEXT;
BEGIN
    -- 生成列名字符串
    SELECT string_agg(DISTINCT quote_ident(field_name), ', ') INTO cols
    FROM original_data;

    -- 拼接crosstab查询语句
    query := format('
        SELECT *
        FROM crosstab(
            ''SELECT id, field_name, field_value FROM original_data ORDER BY 1,2'',
            ''SELECT DISTINCT field_name FROM original_data ORDER BY 1''
        ) AS ct(id INT, %s);', cols);

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

额外说明 #

如果你的原始数据是单字段包含"字段名: 值"格式（比如一列内容是"姓名: 张三 年龄:30"），需要先用字符串拆分函数（比如MySQL的REGEXP_SUBSTR、SQL Server的STRING_SPLIT、PostgreSQL的REGEXP_MATCHES）拆分出字段名和值，再按上述方法转换。

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