MySQL无需UNION ALL实现多列转行及结果存表问题

一、排除acct相关条目 #

原代码会把acct字段当作普通列转换为行数据，只需在查询表结构时过滤掉acct列即可，具体修改如下：

SET SESSION group_concat_max_len = 92160;
SET @target_schema='rd';
SET @target_table='pbc_gl';
SET @target_where='`acct`'; -- 注：此写法等价于过滤acct字段非空的行，需特定过滤逻辑可自行修改，比如`acct = 'xxx'`

SELECT
    GROUP_CONCAT(qry SEPARATOR ' UNION ALL ')
    INTO @sql
FROM (
    SELECT
        CONCAT('SELECT `acct`,', QUOTE(COLUMN_NAME), ' AS `Business_Unit`,`', COLUMN_NAME, '` AS `value` FROM `', @target_table, '` WHERE ', @target_where) qry
    FROM (
        SELECT `COLUMN_NAME` 
        FROM `INFORMATION_SCHEMA`.`COLUMNS` 
        WHERE `TABLE_SCHEMA`=@target_schema 
            AND `TABLE_NAME`=@target_table
            AND COLUMN_NAME != 'acct' -- 新增：排除acct列，避免其被当作数据项混入
    ) AS `A`
) AS `B` ;

二、将查询结果生成新表 #

通过CREATE TABLE ... AS SELECT语法，可直接将动态查询的结果生成新表。完整代码如下（替换new_pivot_table为你需要的新表名）：

SET SESSION group_concat_max_len = 92160;
SET @target_schema='rd';
SET @target_table='pbc_gl';
SET @target_where='`acct`';
SET @new_table_name='new_pivot_table'; -- 定义新表名称

SELECT
    GROUP_CONCAT(qry SEPARATOR ' UNION ALL ')
    INTO @pivot_query
FROM (
    SELECT
        CONCAT('SELECT `acct`,', QUOTE(COLUMN_NAME), ' AS `Business_Unit`,`', COLUMN_NAME, '` AS `value` FROM `', @target_table, '` WHERE ', @target_where) qry
    FROM (
        SELECT `COLUMN_NAME` 
        FROM `INFORMATION_SCHEMA`.`COLUMNS` 
        WHERE `TABLE_SCHEMA`=@target_schema 
            AND `TABLE_NAME`=@target_table
            AND COLUMN_NAME != 'acct' -- 排除acct列
    ) AS `A`
) AS `B` ;

-- 拼接创建新表的SQL语句
SET @sql = CONCAT('CREATE TABLE ', @new_table_name, ' AS ', @pivot_query);

PREPARE s FROM @sql;
EXECUTE s; 
DEALLOCATE PREPARE s;

补充说明 #

1. 若需要自定义新表的字段类型、约束（比如主键、非空），可先手动创建表结构，再插入数据：

-- 手动创建表结构（示例，根据实际需求调整字段类型）
CREATE TABLE new_pivot_table (
    acct VARCHAR(50) NOT NULL,
    Business_Unit VARCHAR(100) NOT NULL,
    value DECIMAL(18,2)
);

-- 插入转换后的数据
SET @sql = CONCAT('INSERT INTO ', @new_table_name, ' ', @pivot_query);
PREPARE s FROM @sql;
EXECUTE s; 
DEALLOCATE PREPARE s;

2. 原代码中@target_where='acct'是过滤acct非空的行，若需精准过滤（比如只保留acct为特定值的行），可修改为@target_where='acct = ''xxx'''（注意字符串转义）。

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