How to pivot columns to rows in MySQL for N number of columns without using Union all

Question

I already went through the details in the link (Mysql Convert Column to row (Pivot table )). As the number of Columns is high and using union all on all of them would be time taking. I decided to use the last resolution in the given link. I was able to run the query the results were:

The issue is the acct getting included as data and also I want to create a table from the result . So Can these entries be excluded and how can I create a table from the results? (new to SQL)

The Code:

SET SESSION group_concat_max_len = 92160;
SET @target_schema='rd';
SET @target_table='pbc_gl';
SET @target_where='`acct`';



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
    ) AS `A`
) AS `B` ;

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

Thank you. Could you let me know who to create a table from this ? new to SQL — Siddhartha, Feb 18 '23 at 04:59
You can prepend `CREATE TABLE new_tbl_name ` to your SELECT statement. — user1191247, Feb 18 '23 at 18:14
SELECT GROUP_CONCAT(qry SEPARATOR ' UNION ALL ') INTO @sql FROM (CREATE TABLE new_tbl_name 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 ) AS `A` ) AS `B` ; LIKE THIS ? — Siddhartha, Feb 19 '23 at 16:21
which part of the query should i place the CREATE TABLE gl_data as? Everywhere its giving error — Siddhartha, Feb 20 '23 at 13:48

score 0 · Answer 1 · answered Feb 21 '23 at 16:53

Add AND COLUMN_NAME != 'acct' to the WHERE condition in the query to exclude that column from the GROUP_CONCAT().

Concatenate CREATE TABLE new_table_name to the beginning of the query to make it create a new table with this result.

SELECT
    CONCAT('CREATE TABLE ', QUOTE(new_table_name), ' AS ',
            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'
    ) AS `A`
) AS `B` ;

How to pivot columns to rows in MySQL for N number of columns without using Union all

1 Answers1

Linked