SQL grouping product balance by company

Question

What I need to do is I need to put together a report that will return the stock of products by individual companies. select must return the following:

+--------------+-----------------+------------------+-----------------+
| nome_produto | saldo_empresa_a | saldo_empresa_b  | saldo_empre_etc |
| Notebook     | saldo           | saldo            |  saldo          |
| Mouse        | saldo           | saldo            |  saldo          |
| Monitor      | saldo           | saldo            |  saldo          |
+--------------+-----------------+------------------+-----------------+

My problem is, I don’t know how many companies own the product, IE, the columns are dynamic I can have a thousand companies or only one. I can have equal products in companies equal to the table above, or I can have company with zero balance or even own this product

Here I’ve set up a database to test SQL in the example above, I have no idea if what I want is possible. Someone has any suggestions?

Answer 1

Try it this way:

SET @sql = NULL;

SELECT  GROUP_CONCAT(DISTINCT CONCAT('SUM(CASE WHEN SE.empresa_id = ', id, ' THEN SE.saldo ELSE 0 END) AS ''', nome, '''')) 
INTO    @sql
FROM    empresa;

SET @sql =  CONCAT('
                    SELECT      P.nome AS produto
                            ,   ', @sql, '
                    FROM        saldo_estoque   SE
                    INNER JOIN  produto         P   ON P.id = SE.produto_id
                    GROUP BY    SE.produto_id');

PREPARE stmt 
FROM    @sql;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

You may need some tweaks because I just saw the structure, not the result.