PIVOT - Group Duplicate Records in a Single Line

Question

I have a problem with a query, in which I need to turn rows into columns. In case I thought to use the function pivot, but it didn’t work very well, since mysql doesn’t provide me with such a function. But the tables are as follows::

My goal is to display the results as follows

However my query is a little awkward, because it returns the same products in different lines and ae can not display this way, even trying to group the columns.

SELECT pq.id_produto,pd.descricao,
       CASE WHEN pl.id_loja = '1' THEN pq.produto_quantidade
       END LOJA1,
       CASE WHEN pl.id_loja = '2' THEN pq.produto_quantidade
       END LOJA2,
       CASE WHEN pl.id_loja = '3' THEN pq.produto_quantidade
       END LOJA3,
       CASE WHEN pl.id_loja = '4' THEN pq.produto_quantidade
       END LOJA4
FROM pedidos_produtos_quantidade pq
LEFT JOIN pedidos_lojas pl ON pl.id = pq.id_pedido_loja
LEFT JOIN lojas l ON pl.id_loja = l.id
LEFT JOIN produtos pd ON pq.id_produto = pd.id_produto
GROUP BY pd.descricao,
         pq.produto_quantidade

This sql generates this result, which I cannot group in the way I mentioned:

Someone could help me make this cluster?

Answer 1

Using the following structure:

CREATE TABLE pedidos_lojas(`id` integer, `id_loja` integer);
CREATE TABLE pedidos_produtos_quantidade(`id` integer, `id_produto` integer, `produto_quantidade` integer, `id_pedido_loja` integer);
CREATE TABLE produtos(`id_produto` integer, `descricao` varchar(100));
CREATE TABLE lojas(`id` integer, `nome` varchar(100));

INSERT INTO pedidos_lojas(`id`, `id_loja`)
VALUES (1, 2),
       (11, 3),
       (12, 4);

INSERT INTO pedidos_produtos_quantidade(`id`, `id_produto`, `produto_quantidade`, `id_pedido_loja`)
VALUES (1, 1, 10, 1),
       (2, 2, 20, 1),
       (12, 1, 15, 11),
       (13, 38, 18, 1),
       (14, 44, 12 ,1),
       (15, 44, 22, 11),
       (16, 44, 10, 12),
       (17, 38, 9, 11),
       (18, 38, 29, 12),
       (19, 1, 15, 12),
       (20, 2, 18, 11);

INSERT INTO produtos(`id_produto`, `descricao`)
VALUES (1, 'Abacate'),
       (2, 'Abacaxi'),
       (38, 'Laranja'),
       (44, 'Manga');

INSERT INTO lojas(`id`, `nome`)
VALUES (1, 'Centro'),
       (2, 'Lagoa'),
       (3, 'Gloria'),
       (4, 'Serra');

Use the following query:

SELECT pq.id_produto,
       pd.descricao,
       sum(CASE pl.id_loja WHEN 1 THEN pq.produto_quantidade ELSE 0 END) as loja1,
       sum(CASE pl.id_loja WHEN 2 THEN pq.produto_quantidade ELSE 0 END) as loja2,
       sum(CASE pl.id_loja WHEN 3 THEN pq.produto_quantidade ELSE 0 END) as loja3,
       sum(CASE pl.id_loja WHEN 4 THEN pq.produto_quantidade ELSE 0 END) as loja4
  FROM pedidos_produtos_quantidade pq
  LEFT JOIN pedidos_lojas pl ON pl.id = pq.id_pedido_loja
  LEFT JOIN lojas l ON pl.id_loja = l.id
  LEFT JOIN produtos pd ON pq.id_produto = pd.id_produto
 GROUP BY pq.id_produto,
          pd.descricao;

Resulting in:

| id_produto | descricao | loja1 | loja2 | loja3 | loja4 |
| ---------- | --------- | ----- | ----- | ----- | ----- |
| 1          | Abacate   | 0     | 10    | 15    | 15    |
| 2          | Abacaxi   | 0     | 20    | 18    | 0     |
| 38         | Laranja   | 0     | 18    | 9     | 29    |
| 44         | Manga     | 0     | 12    | 22    | 10    |

In the query described above we group the data by id of the product and describe a column for each store, checking the code of the same in a CASE within the SUM.

---

See working on DB Fiddle.