PIVOT - SELECT reversing row and column

Asked

Viewed 269 times

3

I have a table like this:

codigo  ano  quant
100     2014   15
100     2015   13
100     2016   20
101     2015   15
102     2016   22
102     2014   05

I want to create a query that lists me the code like this:

codigo   2014   2015   2016
100        15     13     20
101         0     15      0
102         5      0     22
mysql sql database select pivot

  • What you want to do is a 'pivot table', and it will probably require SQL to be mounted dynamically; take a look at this another post of stackoverflow for some ways this can be implemented.

1 answer

3


Considering the following structure:

CREATE TABLE anos(codigo     INTEGER,
                  quantidade INTEGER,
                  ano        INTEGER);

INSERT INTO anos(codigo, ano, quantidade)
VALUES(100, 2014, 15),
      (100, 2015, 13),
      (100, 2016, 20),
      (101, 2015, 15),
      (102, 2016, 22),
      (102, 2014, 05);

Group the results by code and perform the condition within one SUM to check the year. If the year is for column, return the quantity, otherwise return zero:

SELECT a.codigo,
       SUM(CASE a.ano WHEN 2014 THEN a.quantidade ELSE 0 END) AS '2014',
       SUM(CASE a.ano WHEN 2015 THEN a.quantidade ELSE 0 END) AS '2015',
       SUM(CASE a.ano WHEN 2016 THEN a.quantidade ELSE 0 END) AS '2016'
FROM anos a
GROUP BY a.codigo;

Resulting in:

| codigo | 2014 | 2015 | 2016 |
| ------ | ---- | ---- | ---- |
| 100    | 15   | 13   | 20   |
| 101    | 0    | 15   | 0    |
| 102    | 5    | 0    | 22   |

See working on DB Fiddle.

Browser other questions tagged mysql sql database select pivot

You are not signed in. Login or sign up in order to post.