Mysql Pivot some column in 1 table

Question

I have a table like this:

id       key       year   month   value
---------------------------------------
1        AD        2000    1       5465
2        AD        2000    2       6445
3        JK        2000    1       7777
4        JK        2000    2       9999

I need to retrive the values like this:

key       2000-1  2000-2
------------------------
AD        5465    6445
JK        7777    9999

I'm having issues with creating the headers, concatenating year and month and displaying the value under the header.

I have another pivot procedure like this:

SELECT
    GROUP_CONCAT(
        DISTINCT CONCAT(
            'MAX(IF(combustible_id = ''',
            combustible_id,
            ''', valor_combustible, NULL)) AS ',
            CONCAT("`",admin_combustibles.nombre,"`")
        )
    ) INTO @SQL
FROM
    admin_indice_combustibles
INNER JOIN admin_combustibles 
   ON admin_indice_combustibles.combustible_id = admin_combustibles.id_combustible 
WHERE admin_indice_combustibles.estado = 1;

SET @SQL = CONCAT(
    'SELECT anio, mes, ',
    @SQL,
    ' FROM admin_indice_combustibles
      WHERE estado = 1 
      GROUP BY anio, mes 
      ORDER BY id_indice_combustible'
);

PREPARE stmt
FROM
    @SQL;

it is working, but it uses more data (because it has a JOIN with another table), now is easier, all the data is in just 1 table, but I can't get it. any hint please?

EDIT:

I'm trying with this code:

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 the error:

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

I just need to concatenate the year (anio) and month (mes) in the header, and give the value (energia_adjudicada_mwh) to them, for each year and mont, group by key...

This is the table that I have and the table that I need:

Then pls share what you have tried and the exact error message or unexpected behaviour. — Shadow, Apr 02 '18 at 15:27
So the code you show us works but for other tables? And doesn't work with your current table? What is the current query you are using and what result you have? — Juan Carlos Oropeza, Apr 02 '18 at 18:55
It's long, I was trying to make a sql fiddle but can't find a place where my code works... I will put anyway my code but it's not clear what I need with it because it's wrong, besides, in the example that I put I used 3 tables, now I only have to use 1 table. — pmiranda, Apr 02 '18 at 19:17
You should ask a new question instead of adding a second unrelated question to your duplicate question (which isn't likely to get it reopened). — NightOwl888, Apr 03 '18 at 01:47
I've did that here: https://stackoverflow.com/questions/49617874/pivot-table-in-mysql-concatenating-two-rows-name-into-headers but now that one is marked as duplicate... it's a different question, not just asking for generic "pivot mysql" — pmiranda, Apr 03 '18 at 14:31

score 1 · Answer 1 · answered Apr 02 '18 at 16:00

1

Use conditional aggregation like this:

SQL DEMO

SELECT `key`,
        MAX( CASE WHEN `year` = 2000 and  `month` = 1 THEN `value` END) as `2000-01`,
        MAX( CASE WHEN `year` = 2000 and  `month` = 2 THEN `value` END) as `2000-02`
FROM t49613951
GROUP BY `key`;

OUTPUT:

answered Apr 02 '18 at 16:00

Juan Carlos Oropeza

47,252
12
78
118

mmm it's ok, the problem is that I put a demo of my situation, but in the real case I have 21 years, so it would be like 21*12 cases, that's too much. I'm trying to make those headers with a `select` not hard-coded – pmiranda Apr 02 '18 at 18:28
well you should be more specific next time. Also you didn't include all your tables in the original question. Check this example https://stackoverflow.com/questions/12598120/mysql-pivot-table-query-with-dynamic-columns – Juan Carlos Oropeza Apr 02 '18 at 18:45
That's exactly the same procedure that I have and I put in my example – pmiranda Apr 02 '18 at 18:47
Then I fail to see the problem, because you have something is already working. – Juan Carlos Oropeza Apr 02 '18 at 18:48
I'm trying to use that but without the JOIN that I've specified in the body of my question – pmiranda Apr 02 '18 at 18:49
again, why is that a problem? and again you didnt show us what are those tables. – Juan Carlos Oropeza Apr 02 '18 at 18:50

Mysql Pivot some column in 1 table

1 Answers1