MySQL如何将单字段查询结果行转置为多列输出（不使用变量）

开发者阿泽

2026-9-27

纯SELECT实现行转置方案

以下方案均不使用临时变量，仅靠SQL内置功能实现：

通用ANSI SQL方案（支持所有带窗口函数的数据库，返回单行多列结果）

SELECT 
    MAX(CASE WHEN rn = 0 THEN column_name END) AS T0,
    MAX(CASE WHEN rn = 1 THEN column_name END) AS T1,
    MAX(CASE WHEN rn = 2 THEN column_name END) AS T2,
    MAX(CASE WHEN rn = 3 THEN column_name END) AS T3,
    MAX(CASE WHEN rn = 4 THEN column_name END) AS T4,
    MAX(CASE WHEN rn = 5 THEN column_name END) AS T5,
    MAX(CASE WHEN rn = 6 THEN column_name END) AS T6
FROM (
    SELECT 
        column_name, 
        ROW_NUMBER() OVER (ORDER BY ordinal_position) - 1 AS rn
    FROM information_schema.columns 
    WHERE table_name = 'volumestest'
) AS temp;

实现逻辑：

子查询通过ROW_NUMBER窗口函数给每个列名按表结构的原始顺序生成从0开始的序号
外层通过条件聚合把对应序号的列名提取为单独的字段，最终返回单行7列的结果

各数据库拼接输出`|T0|T1|T2|T3|T4|T5|T6|`格式字符串的方案

MySQL / MariaDB

SELECT CONCAT('|', GROUP_CONCAT(column_name ORDER BY ordinal_position SEPARATOR '|'), '|') AS transpose_result
FROM information_schema.columns 
WHERE table_name = 'volumestest';

PostgreSQL

SELECT '|' || STRING_AGG(column_name, '|' ORDER BY ordinal_position) || '|' AS transpose_result
FROM information_schema.columns 
WHERE table_name = 'volumestest';

SQL Server

SELECT '|' + STRING_AGG(column_name, '|') WITHIN GROUP (ORDER BY ordinal_position) + '|' AS transpose_result
FROM information_schema.columns 
WHERE table_name = 'volumestest';

Oracle

SELECT '|' || LISTAGG(column_name, '|') WITHIN GROUP (ORDER BY column_id) || '|' AS transpose_result
FROM user_tab_columns 
WHERE table_name = 'VOLUMESTEST';