mySQL透视动态日期表

Question

我正在运行我的查询

SELECT
DATE_FORMAT(FROM_DAYS(TO_DAYS(st_day_request) -MOD(TO_DAYS(st_day_request) -2, 7)), '%d-%m-%Y') as DATE,
tx_report_id AS 'BUSINESS',
CONCAT(ROUND(AVG(DATEDIFF(dt_dossier_sent,st_day_request) > -5 AND (main.dt_dossier_sent IS NOT NULL AND main.dt_dossier_sent <> '0000-00-00')) * 100),'%') as DISPATCHED_5_AND_MORE,
CONCAT(ROUND(AVG(DATEDIFF(dt_dossier_sent,st_day_request) < -5 AND DATEDIFF(dt_dossier_sent,st_day_request) <= 1 AND (main.dt_dossier_sent IS NOT NULL AND main.dt_dossier_sent <> '0000-00-00')) * 100),'%') as DISPATCHED_4_TO_1,
CONCAT(ROUND(AVG(DATEDIFF(dt_dossier_sent,st_day_request) > 1 AND DATEDIFF(dt_dossier_sent,st_day_request) <= 10 AND (main.dt_dossier_sent IS NOT NULL AND main.dt_dossier_sent <> '0000-00-00')) * 100),'%') as DISPATCHED_2_TO_10,
CONCAT(ROUND(AVG(DATEDIFF(dt_dossier_sent,st_day_request) > 10 AND (main.dt_dossier_sent IS NOT NULL AND main.dt_dossier_sent <> '0000-00-00')) * 100),'%') as DISPATCHED_10_AND_MORE,
CONCAT(ROUND(AVG(main.dt_dossier_sent IS NULL OR main.dt_dossier_sent = '0000-00-00') * 100),'%') as OUTSTANDING FROM
(
SELECT 
    app_contract.id_app,
    app_contract.dt_dossier_sent,
    contract.tx_report_id,
    contract.st_day_request
FROM
    contract
INNER JOIN app_contract ON  app_contract.id_contract = contract.id_contract  AND contract.st_day_request > '2017-09-01' AND contract.id_client = 657
INNER JOIN app_hist ON app_hist.id_app=app_contract.id_app
GROUP BY app_contract.id_app
) as main GROUP BY DATE ORDER BY DATE_FORMAT(st_day_request, '%y') ASC, DATE_FORMAT(st_day_request, '%m') ASC

并在下图中获得结果：

​

​

到目前为止，一切都是正确的。我遇到的问题是，我想要更改(透视/交叉表)我的结果，并将我获得的日期作为列，如下图所示：

​

​

我尝试了几天，但不幸的是没有结果。有什么想法吗？

PS。如果您无法正常查看图像，请右键单击并在新选项卡中打开。然后你可以放大它们。对于这些图片我深表歉意，我会在下一篇文章中复制并粘贴结果。

Answer 1

遵循标记--在MySQL中有许多这样做的示例。此外，还有一种从MySQL存储过程自动生成SQL语句的方法：http://mysql.rjweb.org/doc.php/pivot

(就我个人而言，我使用通用PHP代码将3列表格转换为矩阵。)