I have this table:
TABLE: admin_contratos_energia_adjudicada_distribucion_mensual
And I need a procedure to make this one:
I've made something similar but now I can't figure where should I change my code. My actual code is this one:
BEGIN
SELECT
GROUP_CONCAT(
DISTINCT CONCAT(
' MAX(IF(anio = ''',
DIST.anio,
''' AND mes = ''', DIST.mes, ''', energia_adjudicada_mwh, NULL)) AS ',
CONCAT("`",DIST.anio,"-`", DIST.mes,"`")
)
) INTO @SQL
FROM
admin_contratos_energia_adjudicadas_distribucion_mensual AS DIST
WHERE DIST.activo = 1;
SET @SQL = CONCAT(
'SELECT DIST.key, DIST.contrato_id ',
@SQL,
' FROM admin_contratos_energia_adjudicadas_distribucion_mensual AS DIST
WHERE activo = 1
GROUP BY DIST.key
ORDER BY DIST.contrato_id ');
PREPARE stmt
FROM
@SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END
I'm getting this error:
Procedure execution failed
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'MAX(IF(anio = '2016' AND mes = '1', energia_adjudicada_mwh, NULL)) AS 2016-1`,' at line 1
Any hint? I just need to concatenate the 'anio' and 'mes' in the headers of the new one, and put there the 'energia adjudicada mwh' value.
Please, don't post answers with CASE because the table has like 20 years, I need to retrieve the years with a query.
EDIT: Edited some parts near to mes
BEGIN
SELECT
GROUP_CONCAT(
DISTINCT CONCAT(
'MAX(IF(anio = ''',
anio,
''' AND mes = ''', mes, ''', energia_adjudicada_mwh, NULL)) AS ',
CONCAT("`",mes,"-", anio,"`")
)
) INTO @SQL
FROM
admin_contratos_energia_adjudicadas_distribucion_mensual
WHERE activo = 1;
SET @SQL = CONCAT(
'SELECT key, contrato_id, ',
@SQL,
' FROM admin_contratos_energia_adjudicadas_distribucion_mensual
WHERE activo = 1
GROUP BY key
ORDER BY contrato_id ');
PREPARE stmt
FROM
@SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END
Error:
Procedure execution failed 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'key, contrato_id, MAX(IF(anio = '2016' AND mes = '1', energia_adjudicada_mwh, NU' at line 1
EDIT 2:
I got this with select @SQL:
SELECT key,
contrato_id,
Max(IF(anio = '2016'
AND mes = '1', energia_adjudicada_mwh, NULL)) AS `1-2016`,
Max(IF(anio = '2016'
AND mes = '2', energia_adjudicada_mwh, NULL)) AS `2-2016`,
Max(IF(anio = '2016'
AND mes = '3', energia_adjudicada_mwh, NULL)) AS `3-2016`,
Max(IF(anio = '2016'
AND mes = '4', energia_adjudicada_mwh, NULL)) AS `4-2016`,
Max(IF(anio = '2016'
AND mes = '5', energia_adjudicada_mwh, NULL)) AS `5-2016`,
Max(IF(anio = '2016'
AND mes = '6', energia_adjudicada_mwh, NULL)) AS `6-2016`,
Max(IF(anio = '2016'
AND mes = '7', energia_adjudicada_mwh, NULL)) AS `7-2016`,
Max(IF(anio = '2016'
AND mes = '8', energia_adjudicada_mwh, NULL)) AS `8-2016`,
Max(IF(anio = '2016'
AND mes = '9', energia_adjudicada_mwh, NULL)) AS `9-2016`,
Max(IF(anio = '2016'
AND mes = '10', energia_adjudicada_mwh, NULL)) AS `10-2016`,
Max(IF(anio = '2016'
AND mes = '11', energia_adjudicada_mwh, NULL)) AS `11-2016`,
Max(IF(anio = '2016'
AND mes = '12', energia_adjudicada_mwh, NULL)) AS `12-2016`,
Max(IF(anio = '2017'
AND mes = '1', energia_adjudicada_mwh, NULL)) AS
from admin_contratos_energia_adjudicadas_distribucion_mensual
WHERE activo = 1
GROUP BY KEY
ORDER BY contrato_id
LAST EDIT (with correct answer): if anyone needs the final code... is this:
BEGIN
SET @@group_concat_max_len = 9999;
SET @SQL = NULL;
SELECT
GROUP_CONCAT(
DISTINCT CONCAT(
'MAX(IF(anio = ''',
anio,
''' AND mes = ''', mes, ''', energia_adjudicada_mwh, NULL)) AS ',
CONCAT("`",mes,"-", anio,"`")
)
) INTO @SQL
FROM
admin_contratos_energia_adjudicadas_distribucion_mensual
WHERE activo = 1;
SET @SQL = CONCAT(
'SELECT `key`, contrato_id, ',
@SQL,
' FROM admin_contratos_energia_adjudicadas_distribucion_mensual
WHERE activo = 1
GROUP BY `key`
ORDER BY contrato_id ');
PREPARE stmt
FROM
@SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
#select @SQL;
END
