MYSQL Transpose the rows of the result of a select in column (Only 1 column result)

Question

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

T0
T1
T2
T3
T4
T5
T6

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

You want the result in 7 columns or just one, comma-delimited column? — Salman A, Nov 04 '21 at 13:58
I have 113 rows when I do this command, and i want thoses rows in columns, just a transposition. So yes i want them in column, 1 value column for 1 value row. I want a dynamic transposition because the number of columns will change sometimes. — François Maurice, Nov 04 '21 at 14:01
This topic can help you https://stackoverflow.com/questions/7674786/how-can-i-return-pivot-table-output-in-mysql. Thenumber of columns must be static. — Tohm, Nov 04 '21 at 14:04
yes i saw it the problem is that the number of columns is not static — François Maurice, Nov 04 '21 at 14:05
https://stackoverflow.com/questions/1241178/mysql-rows-to-columns — Ergest Basha, Nov 04 '21 at 14:05
The question Tohm linked has a solution for dynamic columns too, e.g. [this one](https://stackoverflow.com/a/26297463), or maybe have a look at [MySQL pivot table query with dynamic columns](https://stackoverflow.com/q/12598120) — Solarflare, Nov 04 '21 at 15:00

score 0 · Accepted Answer · answered Nov 04 '21 at 15:37

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]>

MYSQL Transpose the rows of the result of a select in column (Only 1 column result)

1 Answers1