来自MySql中表的动态标头

Question

this is my first question. Sorry for my bad English and the mix with Spanish, im from Uruguay.

I have this ER (example) : enter image description here I have a Person (CI) with some relationship with many concepts values. The concepts are Unidad, Clase, Estado, etc. and they have some values asociated like 2021,1061,1003 for "Unidad". For each concepts, theres some fields (Fecha, Expediente, Comentario, Usuario) where the row is the max "Fecha" values of the CI-Concepto. I want to list all the relations of a person, like this:

HEADERS:

CI  |   Concepto1_clave |   Concepto1_Fecha |   Concepto1_Expediente    |   Concepto1_Comentario    |   Concepto1_Usuario   |   Concepto2_clave |   Concepto2_Fecha |   Concepto2_Expediente    |   Concepto2_Comentario    |   Concepto2_Usuario   |   ConceptoN...

VALUES:

Person_CI   |   Value of concepto 1 |   Fecha of concepto 1 |   Expediente of concepto 1    |   Comentairo of concepto 1    |   Usuario of concepto 1   |   Value of concepto 2 |   Fecha of concepto 2 |   Expediente of concepto 2    |   Comentairo of concepto 2    |   Usuario of concepto 2   |   ...

All must be dynamic, when i create a new concept, it will appear on query. I try with many querys and procedures and dont have succes. Im using PHP to get the result. Thank u so much for your help!!

EDIT:

There are my tables:

Persona_ClaveConcepto

    +--------+-------------------+------------+---------+---------------+---------------+---------+
| CI     | Concepto          | Fecha      | Clave   | Expediente    | Comentario    | Usuario |
+--------+-------------------+------------+---------+---------------+---------------+---------+
| 238351 | Clase             | 1997-06-10 | X.C0.1E | FOR06/97      | CARGA SISTEMA |         |
+--------+-------------------+------------+---------+---------------+---------------+---------+
| 238351 | Situacion laboral | 1997-06-01 | 52      | FOR06/97      | CARGA SISTEMA |         |
+--------+-------------------+------------+---------+---------------+---------------+---------+
| 238351 | Unidad            | 2015-07-16 | 1532    | CARGA SISTEMA |               |         |
+--------+-------------------+------------+---------+---------------+---------------+---------+
| 612344 | Clase             | 0000-00-00 | X.C0.1E | CARGA SISTEMA |               |         |
+--------+-------------------+------------+---------+---------------+---------------+---------+
| 612344 | Situacion laboral | 1996-03-01 | 52      | HAB           | CARGA SISTEMA |         |
+--------+-------------------+------------+---------+---------------+---------------+---------+

Expected result (something like that) with all of "Concepto":

    +--------+---------+-------------+------------------+------------------+---------------+------------------+------------------------+-----------------------------+-----------------------------+--------------------------+
| CI     | Clase   | Fecha_Clase | Expediente_Clase | Comentario_Clase | Usuario_Clase | SituacionLaboral | Fecha_SituacionLaboral | Expediente_SituacionLaboral | Comentario_SituacionLaboral | Usuario_SituacionLaboral |
+--------+---------+-------------+------------------+------------------+---------------+------------------+------------------------+-----------------------------+-----------------------------+--------------------------+
| 238351 | X.C0.1E | 1997-06-10  | FOR06/97         | CARGA SISTEMA    |               | 52               | 1997-06-01             | FOR06/97                    | CARGA SISTEMA               |                          |
+--------+---------+-------------+------------------+------------------+---------------+------------------+------------------------+-----------------------------+-----------------------------+--------------------------+

Answer 1

Thanks a lot! I solve it with this query. Hope can help someone else.

/* GROUP CONCAT LIMIT*/
SET SESSION group_concat_max_len = 1000000;

/* DECLARE */
SET @sqlMax = NULL;
SET @sqlCase = NULL;
SET @sql = NULL;

/* SET sqlMax (discard empty values) */
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'GROUP_CONCAT(',replace(Concepto, ' ', ''),') AS ',replace(Concepto, ' ', ''),
      ',GROUP_CONCAT(',replace(Concepto, ' ', ''),'_Fecha) AS ',replace(Concepto, ' ', ''),'_Fecha',
      ',GROUP_CONCAT(',replace(Concepto, ' ', ''),'_Expediente) AS ',replace(Concepto, ' ', ''),'_Expediente',
      ',GROUP_CONCAT(',replace(Concepto, ' ', ''),'_Comentario) AS ',replace(Concepto, ' ', ''),'_Comentario',
      ',GROUP_CONCAT(',replace(Concepto, ' ', ''),'_Usuario) AS ',replace(Concepto, ' ', ''),'_Usuario'
    )
  ) 
INTO @sqlMax
from persona_claveconceptoperseo;

/* SET sqlCase */
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'case when Concepto = ''',Concepto,''' then Clave end AS ',replace(Concepto, ' ', ''),
      ',case when Concepto = ''',Concepto,''' then Fecha end AS ',replace(Concepto, ' ', ''),'_Fecha'
      ',case when Concepto = ''',Concepto,''' then Expediente end AS ',replace(Concepto, ' ', ''),'_Expediente'
      ',case when Concepto = ''',Concepto,''' then Comentario end AS ',replace(Concepto, ' ', ''),'_Comentario'
      ',case when Concepto = ''',Concepto,''' then Usuario end AS ',replace(Concepto, ' ', ''),'_Usuario'
    )
  ) 
INTO @sqlCase
from persona_claveconceptoperseo;

/* SET sql */
SET @sql = CONCAT('SELECT CI, ', @sqlMax, ' 
                  FROM (
                    SELECT
                    CI,',@sqlCase,'
                  FROM persona_claveconceptoperseo) as t1
GROUP BY t1.CI');

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