MySQL: Pivot (rows for columns) of columns with the result of a query dynamically

Navigation
1

I have two sets of tables below:

1:

2:

IputaquerythatreturnstheresultsofthetwotablesusingUnion:

Selecttbprojeto.projNomeAsProjeto,tbindp.indPNomeAs'Tipo(Ind/Idx)',tbindc.indcValorAsValor,tbproj_cenario.projCenNomeAsCenário,tbidxind_grp.idxIndGrpNomeAs'GrupodeÁnalise'FromtbprojetoInnerJointbindpOntbindp.indPProj_Id=tbprojeto.projIdInnerJointbindcOntbindc.indcInd_Id=tbindp.indPIdInnerJointbproj_cenarioOntbproj_cenario.projCenProj_Id=tbprojeto.projIdAndtbindc.indcCen_Id=tbproj_cenario.projCenIdInnerJointbidxind_grpOntbindp.indPIdxIndGrp_Id=tbidxind_grp.idxIndGrpIdUnionSelecttbprojeto.projNomeAsProjeto,tbidxp.idxPNomeAs'Tipo(Ind/Indx)',tbidxc.IdxCValorAsValor,tbproj_cenario.projCenNomeAsCenário,tbidxind_grp.idxIndGrpNomeFromtbprojetoInnerJointbidxpOntbidxp.idxPProj_Id=tbprojeto.projIdInnerJointbidxcOntbidxc.IdxCIdx_Id=tbidxp.idxPIdInnerJointbproj_cenarioOntbproj_cenario.projCenProj_Id=tbprojeto.projIdAndtbidxc.IdxCProjCen_Id=tbproj_cenario.projCenIdInnerJointbidxind_grpOntbidxp.idxPIdxIndGrp_Id=tbidxind_grp.idxIndGrpId

Here'stheresult:

I'mtryingtopivotthisqueryspecificallysothateachvalueinthescenariocolumnisanewcolumnwithjoinwiththeValuecolumn,theresultwouldlooksomethinglikethis:

Aftermuchworkandhelpfromthestaff,Igotthis(below)withthiscode:

SET@sql=NULL;SELECTGROUP_CONCAT(DISTINCTCONCAT('casewhenprojCenNome="',
      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 Name case, are not transposed, thus repeating, how could I circumvent this?

    
asked by anonymous 15.10.2015 / 03:36

1 answer

2
  

I have edited the answer to help in this step:

Put a value of zero if it is not worth it: 

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's the example: link

    
15.10.2015 / 04:45
Is it possible to shorten the startup time of my application? How to display Questions retrieved from a DAO on the same JSF page