MySQL pivot row to column dynamically

Question

i have table:day

id | jour
1    Lundi 01 Août 2016
2    Mardi 02 Août 2016
3    Mercredi 03 Août 2016
4    Jeudi 04 Août 2016

there will be 50 rows i want output like:

Lundi 01 Août 2016  Mardi 02 Août 2016  Mercredi 03 Août 2016
1                   2                   3

So you want each day in the month to be pivoted to a separate column? I think you will need dynamic SQL for this, if for no other reason than each month can have a variable number of days. — Tim Biegeleisen, Aug 02 '16 at 13:01
Possible duplicate of [MySQL pivot row into dynamic number of columns](http://stackoverflow.com/questions/12004603/mysql-pivot-row-into-dynamic-number-of-columns) — Asdrubal, Aug 02 '16 at 13:47

Bernd Buffen · Accepted Answer · 2016-08-02T14:37:08.600

you can do it easy with this queries: first generate the query and then execute it as prepared statement.

SET SESSION group_concat_max_len = 1000000;

SELECT
  CONCAT('SELECT ',
    GROUP_CONCAT(
      CONCAT (
        "MAX(IF(`jour` = '",jour,'\',id,NULL)) AS `',jour,'`'
      )
    ),
    ' FROM `day`'
  ) INTO @SQL
FROM
 ( SELECT *
   FROM `day`
   ORDER BY id
   LIMIT 1,2
) AS data;

select @SQL;  -- see the query only for debug

PREPARE stmt FROM @SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

sample

MariaDB [yourSchema]> select * from day;
+----+------------------------+
| id | jour                   |
+----+------------------------+
|  1 | Lundi 01 Août 2016     |
|  2 | Mardi 02 Août 2016     |
|  3 | Mercredi 03 Août 2016  |
|  4 | Jeudi 04 Août 2016     |
+----+------------------------+
4 rows in set (0.00 sec)

MariaDB [yourSchema]> SELECT
    ->   CONCAT('SELECT ',
    ->     GROUP_CONCAT(
    ->       CONCAT (
    ->         "MAX(IF(`jour` = '",jour,'\',id,NULL)) AS `',jour,'`'
    ->       )
    ->     ),
    ->     ' FROM `day`'
    ->   ) INTO @SQL
    -> FROM `day`;
Query OK, 1 row affected (0.00 sec)

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

MariaDB [yourSchema]> EXECUTE stmt;
+---------------------+---------------------+------------------------+---------------------+
| Lundi 01 Août 2016  | Mardi 02 Août 2016  | Mercredi 03 Août 2016  | Jeudi 04 Août 2016  |
+---------------------+---------------------+------------------------+---------------------+
|                   1 |                   2 |                      3 |                   4 |
+---------------------+---------------------+------------------------+---------------------+
1 row in set (0.00 sec)

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

MariaDB [yourSchema]>

PREPARE stmt FROM @SQL give error #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL' at line 1 — Sunil Dabhi, Aug 02 '16 at 14:06
@Sunil Dabhi - execute this **SET SESSION group_concat_max_len = 1000000;** before the first query. it is possible that the generated string is longer as the default. — Bernd Buffen, Aug 02 '16 at 14:10
@Sunil Dabhi - can you post the output from **select @SQL** execute it instead of PREPARE to see the generated query. i put in in my answer — Bernd Buffen, Aug 02 '16 at 14:21
@Bernd Buffen Maybe you can help me. Look at this : https://stackoverflow.com/questions/51832979/how-to-create-select-dynamic-fields-from-a-table-in-mysql — moses toh, Aug 14 '18 at 03:17

MySQL pivot row to column dynamically

1 Answers1