How to pivot a column showing its content as new columns I'm working with MySQL 5.6 and I'm having headaches with this issue. I've been looking in several groups the result, but I think this isn't very common.

I have a table like the following one:

direc       pro_id  program                                       project
----------  ------  ---------------------------------------       -------
31111-0201  191     Empresa Exportadora                           K0002
31111-0201  192     Apertura de Empresas                          K0002
31111-0201  223     Promoción y Atracción de Inversiones...       K0002
31111-0201  191     Empresa Exportadora                           K0003
31111-0201  192     Apertura de Empresas                          K0003
31111-0201  223     Promoción y Atracción de Inversiones...       K0003

31111-0501  190     Ingreso de detenidos en centro de detención   E0045
31111-0501  190     Ingreso de detenidos en centro de detención   E0159
31111-0501  190     Ingreso de detenidos en centro de detención   O0059
31111-0501  190     Ingreso de detenidos en centro de detención   P0042
31111-0501  190     Ingreso de detenidos en centro de detención   R0043

31111-0601  224     Administración de servicios básicos           O0060
31111-0601  228     Análisis Jurídico en las liquidaciones        O0060
31111-0601  232     Administración de seguros por siniestralidad  O0060
31111-0601  224     Administración de servicios básicos           U0135
31111-0601  228     Análisis Jurídico en las liquidaciones        U0135
31111-0601  232     Administración de seguros por siniestralidad  U0135


I need a kind of pivot, but every project would be a new column and the result could be 1 or n columns (projects), the content of those columns would be a zero (that will be captured with percentages).

I've separated with a line the data cause the result I'm looking for is by direc, in this case I need to show as columns the projects K0002 and K0003 for the 31111-0201 direc, the 31111-0501 direc have the result of five and that would be columns and so on.

Following this sample the result I need is like this (I did a dynamic table in Excel for the sample):





Thanks in advance for your help.
Found the answer in the next url:
https://vitobotta.com/2015/12/10/mysql-dynamic-column-name/
And I post the example for those who have a similar problem

The final query is:  

DROP TABLE IF EXISTS tmp_proyecto;

CREATE TEMPORARY TABLE tmp_proyecto AS
SELECT DISTINCTROW a.pro_id, a.programa, b.proyecto, CAST(0 AS DECIMAL(8,2)) as total 
FROM programas as a
INNER JOIN presupuesto_aprobado as b ON a.direc = b.direc
WHERE a.direc='31111-0601' AND a.pro_ano=2018
ORDER BY proyecto, pro_id;

SELECT CONCAT('
SELECT pro_id, programa, ',proyectos,'
FROM tmp_proyecto
GROUP BY pro_id
ORDER BY pro_id'
)
INTO @query
FROM
(
SELECT GROUP_CONCAT(CONCAT('IFNULL(MAX(CASE WHEN proyecto=''',proy_actual,''' THEN total END), ''-'') AS "',col_name,'"')) proyectos
FROM (
SELECT proy_actual, CONCAT('P_',proy_actual) AS col_name
FROM (SELECT DISTINCT proyecto AS proy_actual FROM tmp_proyecto) AS unicos
) proy_como_campos
) result;

PREPARE statement FROM @query;
EXECUTE statement;
DEALLOCATE PREPARE statement;


As a result:

pro_id  programa                              P_K0002   P_K0003
191     Empresa Exportadora                   0.00      0.00
192     Apertura de Empresas                  0.00      0.00
223     Promoción y Atracción de Inversiones… 0.00      0.00  


Thanks anyway