0

I am trying to dynamically convert rows to columns in MySQL.

Source table:

 start_period      
  2022-10-10
  2022-10-11
  2022-10-12
  2022-10-13
  2022-10-14
  ...

Result should be like

2022-10-10   2022-10-11   2022-10-12   2022-10-13   2022-10-14   ... 
   null         null         null        null           null
   null         null         null        null           null

These are the dates I get from json and put them into temporary table. And result also need to be in temporary table.

Code to put dates into temporary table:

    DROP TEMPORARY TABLE IF EXISTS tempDateTable;
CREATE TEMPORARY TABLE tempDateTable
WITH RECURSIVE cte AS 
(
  (
  SELECT 
    tempJsonTable.duration_day, 
    tempJsonTable.start_period, 
    tempJsonTable.end_period
  FROM tempJsonTable
  LIMIT 1
  )
  UNION ALL
  SELECT 
    cte.duration_day, 
    cte.start_period + INTERVAL 1 DAY , 
    cte.end_period
  FROM cte
  WHERE cte.start_period < cte.end_period
) 
SELECT
  cte.start_period
FROM cte;
SavalX
  • 1
  • You cannot pivot the data dynamically in single query. You need in stored procedure which uses dynamic SQL. – Akina Oct 25 '22 at 08:23
  • Maybe you can help me with example of dynamic SQL, because I can't implement this and the dates still remain in the rows( – SavalX Oct 25 '22 at 08:26
  • See, for example, [this solution](https://stackoverflow.com/a/66136355/10138734). – Akina Oct 25 '22 at 08:31

0 Answers0