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 the code like this:
codigo 2014 2015 2016
100 15 13 20
101 0 15 0
102 5 0 22
Using the following link I got the following result:
SELECT a.codigo,
sum(a.quantidade*(1-abs(sign(a.ano-2014)))) as 2014,
sum(a.quantidade*(1-abs(sign(a.ano-2015)))) as 2015,
sum(a.quantidade*(1-abs(sign(a.ano-2016)))) as 2016
FROM anos a
GROUP BY a.codigo
The creation of this bank follows the following script:
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);
The following explanation is in the link quoted above:
"pivot table" or "crosstab report" Function SQL feature: Do this without "if", "case", or "GROUP_CONCAT".
The secret and also the reason it works in almost all databases are the following functions:
- sign (x) returns -1.0, +1 for values x < 0, x = 0, x > 0 respectively;
- abs (sign (x)) returns 0 if x = 0 if not, 1 if x> 0 or x < 0;
- 1-abs (sign (x)) complements the above, since it returns 1 only if x = 0.
What you're trying to do is a 'pivot table', and will probably require the SQL to be mounted dynamically; take a look at this other stackoverflow post for some ways how this can be implemented.