I want transpose the result of this request in column

SELECT column_name 
FROM information_schema.columns 
WHERE table_name IN ('volumestest');

The result is this

I WANT this

|T0|T1|T2|T3|T4|T5|T6|

I would like do this with options in the select if its possible and not with a variable storage.

Thanks u

CodePudding user response：

You can do this with a dynamic statement and PREPARE STATEMENT like this:

SELECT CONCAT("SELECT "
        , GROUP_CONCAT( column_name ) 
        , " FROM MYTABLE"
        , " WHERE 1=1") into @myQuery
FROM information_schema.columns 
WHERE table_name = 'MYTABLE'
 AND TABLE_SCHEMA = 'textil';

SELECT @myQuery;

PREPARE stmt FROM @MYQUERY;
EXECUTE stmt;

DEALLOCATE PREPARE stmt;

sample

MariaDB [textil]> SELECT * from MYTABLE;
 ---- ------ ------ ------ 
| T0 | T1   | T2   | T3   |
 ---- ------ ------ ------ 
|  1 |    2 |    4 |    8 |
|  2 |    4 |    8 |   16 |
 ---- ------ ------ ------ 
2 rows in set (0.00 sec)

MariaDB [textil]> SELECT CONCAT("SELECT "
    ->  , GROUP_CONCAT( column_name ) 
    ->  , " FROM MYTABLE"
    -> , " WHERE 1=1") into @myQuery
    -> FROM information_schema.columns 
    -> WHERE table_name = 'MYTABLE'
    -> AND TABLE_SCHEMA = 'textil';
Query OK, 1 row affected (0.01 sec)

MariaDB [textil]> 
MariaDB [textil]> SELECT @myQuery;
 ------------------------------------------- 
| @myQuery                                  |
 ------------------------------------------- 
| SELECT T0,T1,T2,T3 FROM MYTABLE WHERE 1=1 |
 ------------------------------------------- 
1 row in set (0.00 sec)

MariaDB [textil]> 
MariaDB [textil]> PREPARE stmt FROM @MYQUERY;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

MariaDB [textil]> EXECUTE stmt;
 ---- ------ ------ ------ 
| T0 | T1   | T2   | T3   |
 ---- ------ ------ ------ 
|  1 |    2 |    4 |    8 |
|  2 |    4 |    8 |   16 |
 ---- ------ ------ ------ 
2 rows in set (0.01 sec)

MariaDB [textil]> 
MariaDB [textil]> DEALLOCATE PREPARE stmt;
Query OK, 0 rows affected (0.00 sec)

MariaDB [textil]>