如何用SQL实现行转列：转换基因表达表为指定格式

看起来你需要把长格式的基因表达数据表转成宽表——也就是把每个样本作为单独的列，每个基因作为一行展示对应的表达值，这其实是SQL里很常见的**行转列（Pivot）**需求，不同数据库的实现方式略有不同，我给你整理几种常用方案：

首先明确前提：假设你的原表名为gene_expression，字段分别是sample_name（比如'Father 1'这类样本标识）、gene_name（基因名称）、expression_value（表达量数值）。

方案一：静态行转列（适用于样本数量固定的情况） #

如果你的样本列表不会经常变动，直接用CASE WHEN配合GROUP BY就能实现，几乎所有数据库都支持这种写法：

SELECT
    gene_name AS Gene,
    MAX(CASE WHEN sample_name = 'Father 1' THEN expression_value END) AS Father_1,
    MAX(CASE WHEN sample_name = 'Father 2' THEN expression_value END) AS Father_2,
    MAX(CASE WHEN sample_name = 'Q83X 1' THEN expression_value END) AS Q83X_1,
    MAX(CASE WHEN sample_name = 'Q83X 2' THEN expression_value END) AS Q83X_2,
    MAX(CASE WHEN sample_name = 'Rescue 1' THEN expression_value END) AS Rescue_1,
    MAX(CASE WHEN sample_name = 'Rescue 2' THEN expression_value END) AS Rescue_2
FROM gene_expression
GROUP BY gene_name;

这里的MAX函数是为了在按基因分组后，保留每个样本对应的唯一表达值（因为每个基因+样本组合只有一条数据，用MIN或AVG也能得到同样结果）。

方案二：针对特定数据库的简化写法 #

PostgreSQL（使用crosstab函数） #

PostgreSQL有专门的行转列函数crosstab，不过需要先启用tablefunc扩展：

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

然后执行转置查询：

SELECT * FROM crosstab(
    -- 第一个参数：源数据查询，按基因和样本排序
    'SELECT gene_name, sample_name, expression_value FROM gene_expression ORDER BY 1,2',
    -- 第二个参数：指定要转成列的所有样本值
    'SELECT DISTINCT sample_name FROM gene_expression ORDER BY 1'
) AS ct(
    -- 定义结果表的结构
    Gene text,
    Father_1 numeric,
    Father_2 numeric,
    Q83X_1 numeric,
    Q83X_2 numeric,
    Rescue_1 numeric,
    Rescue_2 numeric
);

SQL Server（使用内置PIVOT函数） #

SQL Server内置了PIVOT语法，写法更简洁：

SELECT
    gene_name AS Gene,
    [Father 1] AS Father_1,
    [Father 2] AS Father_2,
    [Q83X 1] AS Q83X_1,
    [Q83X 2] AS Q83X_2,
    [Rescue 1] AS Rescue_1,
    [Rescue 2] AS Rescue_2
FROM (
    -- 子查询：获取源数据的核心字段
    SELECT gene_name, sample_name, expression_value
    FROM gene_expression
) AS SourceTable
PIVOT (
    -- 指定聚合函数（这里用MAX，因为每个组合只有一个值）
    MAX(expression_value)
    -- 指定要转成列的字段和对应的列值
    FOR sample_name IN ([Father 1], [Father 2], [Q83X 1], [Q83X 2], [Rescue 1], [Rescue 2])
) AS PivotTable;

方案三：动态行转列（适用于样本数量动态变化的情况） #

如果以后会新增样本，不想每次都修改SQL语句，可以用动态SQL自动生成列名，以MySQL为例：

-- 第一步：动态生成所有样本对应的CASE WHEN语句
SET @sql = NULL;
SELECT
    GROUP_CONCAT(DISTINCT
        CONCAT(
            'MAX(CASE WHEN sample_name = ''',
            sample_name,
            ''' THEN expression_value END) AS `',
            REPLACE(sample_name, ' ', '_'), -- 把空格换成下划线，符合列名规范
            '`'
        )
    ) INTO @sql
FROM gene_expression;

-- 第二步：拼接完整的查询语句
SET @sql = CONCAT('SELECT gene_name AS Gene, ', @sql, ' FROM gene_expression GROUP BY gene_name');

-- 第三步：执行动态生成的SQL
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

这个脚本会自动读取表中所有唯一的sample_name，生成对应的列，新增样本后直接运行就能自动更新结果。

注意事项 #

• 确保expression_value字段是数值类型（比如numeric、float），避免字符串转换导致的错误；
• 如果某个基因在某个样本中没有数据，结果中对应的列会显示NULL，可以用COALESCE函数替换成0或其他默认值，比如COALESCE(MAX(...), 0)。

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