Mysql: Pivot (p rows/ columns) columns with the result of a query dynamically

Question

I have two sets of tables below:

1:

2:

I created a query that brings the results of the two tables using Union:

Select
  tbprojeto.projNome As Projeto,
  tbindp.indPNome As `Tipo(Ind/Idx)`,
  tbindc.indcValor As Valor,
  tbproj_cenario.projCenNome As Cenário,
  tbidxind_grp.idxIndGrpNome As `Grupo de Ánalise`
From
  tbprojeto Inner Join
  tbindp On tbindp.indPProj_Id = tbprojeto.projId Inner Join
  tbindc On tbindc.indcInd_Id = tbindp.indPId Inner Join
  tbproj_cenario On tbproj_cenario.projCenProj_Id = tbprojeto.projId And
    tbindc.indcCen_Id = tbproj_cenario.projCenId Inner Join
  tbidxind_grp On tbindp.indPIdxIndGrp_Id = tbidxind_grp.idxIndGrpId
Union
Select
  tbprojeto.projNome As Projeto,
  tbidxp.idxPNome As `Tipo (Ind/Indx)`,
  tbidxc.IdxCValor As Valor,
  tbproj_cenario.projCenNome As Cenário,
  tbidxind_grp.idxIndGrpNome
From
  tbprojeto Inner Join
  tbidxp On tbidxp.idxPProj_Id = tbprojeto.projId Inner Join
  tbidxc On tbidxc.IdxCIdx_Id = tbidxp.idxPId Inner Join
  tbproj_cenario On tbproj_cenario.projCenProj_Id = tbprojeto.projId And
    tbidxc.IdxCProjCen_Id = tbproj_cenario.projCenId Inner Join
  tbidxind_grp On tbidxp.idxPIdxIndGrp_Id = tbidxind_grp.idxIndGrpId

That is the result:

I’m trying to pivot this query specifically so that each scenario column value is a new column with Join with the Value column, the result would be something visually like this:

After a lot of work and help from the staff, I got this (below) with this code:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'case when projCenNome = "',
      projCenNome,
      '" then indcValor end AS \'',
      projCenNome, '\''
    )
  ) INTO @sql
from (select projCenNome from tbproj_cenario 
        inner join tbindc on tbindc.indcCen_Id = tbproj_cenario.projCenId 
       ) A;

SET @sql = CONCAT('Select  tbindp.`indPNome` As Valor , ', @sql, '
From tbindc
  Inner Join
  tbproj_cenario On tbindc.indcCen_Id = tbproj_cenario.projCenId 
  Inner Join
  tbindp On tbindc.indcInd_Id = tbindp.indPId');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

That is, the values of the other columns, in the case Name, are not transposed, so repeating, how could you get around this?

Answer 1

I edited the answer to help at this stage:

Put a zeroed value if it has no value for this:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'case when projCenNome = "',
      projCenNome,
      '" then indcValor else 0 end AS \'',
      projCenNome, '\''
    )
  ) INTO @sql
from (select projCenNome from tbproj_cenario 
        inner join tbindc on tbindc.indcCen_Id = tbproj_cenario.projCenId 
       ) A;

SET @sql = CONCAT('Select  tbindp.`indPNome` As Valor , ', @sql, '
From tbindc
  Inner Join
  tbproj_cenario On tbindc.indcCen_Id = tbproj_cenario.projCenId 
  Inner Join
  tbindp On tbindc.indcInd_Id = tbindp.indPId WHERE indcValor is not null');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Here the example: http://sqlfiddle.com/#! 2/c9b388/5