How to dynamic pivot mysql table?-CodePudding

Hi I've got the following mysql table:

I need to pivot these datas to look like the following:

The scadenza is not always the same and can change therefore I believe I need to create the pivot table dynamically, This is the stored procedure I've tried:

BEGIN
SELECT

    GROUP_CONCAT(
    CONCAT("MAX(IF(scadenza='", scadenza, "',importo ,'')) AS '", scadenza, "'"), "
"
      )INTO @answers
    FROM (
      SELECT DISTINCT scadenza,  
                      importo
                 FROM ripartizione_rate
                 
    ) A;

    SET @query :=
      CONCAT(
        'SELECT DISTINCT condomino, anagrafica,', @answers,
          'FROM ripartizione_rate
       GROUP BY condomino, anagrafica'
      );
      
PREPARE statement FROM @query;
    EXECUTE statement;
END

The result I get is very close but I get the date repeated as you can see in the image below:

Can please anyboby halp me to fix this problem? Many thanks for your help

This is the dump text for the table

INSERT INTO `ripartizione_rate` (`id`, `preventivo`, `piano_rateale`, `condomino`, `anagrafica`, `immobile`, `descrizione`, `scadenza`, `stato_pagamento`, `importo`, `importo_pagato`, `importo_residuo`) VALUES
(1, 1, 1, 19, 11, 3, 'Rata Num.1', '2021-01-01', 0, '208.38', '0.00', '0.00'),
(2, 1, 1, 12, 15, 3, 'Rata Num.1', '2021-01-01', 0, '208.38', '0.00', '0.00'),
(3, 1, 1, 10, 15, 5, 'Rata Num.1', '2021-01-01', 0, '500.10', '0.00', '0.00'),
(4, 1, 1, 20, 17, 3, 'Rata Num.1', '2021-01-01', 0, '83.35', '0.00', '0.00'),
(5, 1, 1, 19, 11, 3, 'Rata Num.2', '2021-05-01', 0, '208.31', '0.00', '0.00'),
(6, 1, 1, 12, 15, 3, 'Rata Num.2', '2021-05-01', 0, '208.31', '0.00', '0.00'),
(7, 1, 1, 10, 15, 5, 'Rata Num.2', '2021-05-01', 0, '499.95', '0.00', '0.00'),
(8, 1, 1, 20, 17, 3, 'Rata Num.2', '2021-05-01', 0, '83.33', '0.00', '0.00'),
(9, 1, 1, 19, 11, 3, 'Rata Num.3', '2021-09-01', 0, '208.31', '0.00', '0.00'),
(10, 1, 1, 12, 15, 3, 'Rata Num.3', '2021-09-01', 0, '208.31', '0.00', '0.00'),
(11, 1, 1, 10, 15, 5, 'Rata Num.3', '2021-09-01', 0, '499.95', '0.00', '0.00'),
(12, 1, 1, 20, 17, 3, 'Rata Num.3', '2021-09-01', 0, '83.33', '0.00', '0.00');

CodePudding user response：

The subquery part here:

SELECT
    GROUP_CONCAT(
    CONCAT("MAX(IF(scadenza='", scadenza, "',importo ,'')) AS '", scadenza, "'"), "
"
      )INTO @answers
    FROM (
      SELECT DISTINCT scadenza,          <----
                      importo            <---- this subquery
                 FROM ripartizione_rate  <---- 
    ) A

returns the following result:

 ------------ --------- 
| scadenza   | importo |
 ------------ --------- 
| 2021-01-01 |  208.38 |
| 2021-01-01 |  500.10 |
| 2021-01-01 |   83.35 |
| 2021-05-01 |  208.31 |
| 2021-05-01 |  499.95 |
| 2021-05-01 |   83.33 |
| 2021-09-01 |  208.31 |
| 2021-09-01 |  499.95 |
| 2021-09-01 |   83.33 |
 ------------ ---------

Each date returns 3 rows due to the DISTINCT combination of scadenza, importo. If you run SELECT @answers; after the variable being assigned then you'll get:

SELECT @answers;
 ------------------------------------------------------------- 
|  @answers                                                   |
 ------------------------------------------------------------- 
| MAX(IF(scadenza='2021-01-01',importo ,'')) AS '2021-01-01'  |
| ,MAX(IF(scadenza='2021-01-01',importo ,'')) AS '2021-01-01' |
| ,MAX(IF(scadenza='2021-01-01',importo ,'')) AS '2021-01-01' |
| ,MAX(IF(scadenza='2021-05-01',importo ,'')) AS '2021-05-01' |
| ,MAX(IF(scadenza='2021-05-01',importo ,'')) AS '2021-05-01' |
| ,MAX(IF(scadenza='2021-05-01',importo ,'')) AS '2021-05-01' |
| ,MAX(IF(scadenza='2021-09-01',importo ,'')) AS '2021-09-01' |
| ,MAX(IF(scadenza='2021-09-01',importo ,'')) AS '2021-09-01' |
| ,MAX(IF(scadenza='2021-09-01',importo ,'')) AS '2021-09-01' |
 -------------------------------------------------------------

Whereas what you really want is just 3 distinctive dates instead of 3x3 distinctive dates. Therefore, the fix is quite simple really, you just need to remove the column importo from the subquery:

SELECT
    GROUP_CONCAT(
    CONCAT("MAX(IF(scadenza='", scadenza, "',importo ,'')) AS '", scadenza, "'"), "") INTO @answers
    FROM (SELECT DISTINCT scadenza
            FROM ripartizione_rate
    ) A;
    
 SELECT @answers;
 SET @query :=
      CONCAT(
        'SELECT DISTINCT condomino, anagrafica,', @answers2,
          'FROM ripartizione_rate
       GROUP BY condomino, anagrafica
       ORDER BY condomino, anagrafica'
      );

SELECT @query;

PREPARE statement FROM @query;
EXECUTE statement;

Demo fiddle