SQL Procedure improvement

Question

SQL Procedure improvement

Navigation

#1 by (1 votes)

1

I would like to know if there would be a way to improve a code I made on hand in SQL ... I have a table of the following Table

    Chave identificadora - DataProduto - Preço - Marca
    Lanche - Pao/presunto - 01/01/2017 - 5.90 - McDonalds
    Lanche - Pao/presunto - 01/01/2017 - 5.90 - McDonalds
    Lanche - Pao/presunto - 01/02/2017 - 5.90 - McDonalds
    Jantar- Pao/presunto - 01/01/2017 - 5.90 - Burger King
    Almoço- Pao/presunto - 01/01/2017 - 5.90 - KFC

Being the first line of my header I need to group this and turn the tags into columns so I did the following way:

SELECT [Chave identificadora], DataProduto,SUM(McDonalds)McDonalds, SUM(BurgerKing)BurgerKing,SUM(KFC)[KFC]

 FROM (
SELECT DISTINCT [Chave identificadora], DataProduto,
CASE WHEN MARCA = 'McDonalds' THEN Preço ELSE 0 END as McDonalds,
CASE WHEN MARCA = 'Burger King' THEN Preço ELSE 0 END as BurgerKing,
CASE WHEN MARCA = 'KFC' THEN Preço ELSE 0 END as KFC
from Tabela_Produtos
) AS A  


group by chaveidentificadora,dataproduto

Order by dataproduto

This will return me exactly

Chave - Data - McDonalds - BurgerKing - KFC
Lanche - 01/01/2017 - 11.80 - 5.90 - 5.90
Lanche - 01/02/2017 - 5.90 - 0.00 - 0.00

That is, that's correct! but in this case I wrote 3 marks (bk, kfc and mc) if I had 500 marks I would have to write in hand. My doubt would be, would I have a way to improve this by making it dependent on 1.5.10 thousand records I run a certain code / procedure that will leave it ready? (I apologize if I have not been clear enough)

sql-server

asked by anonymous 16.04.2018 / 00:07

1 answer

How can I not allow the browser to save the entered password? [duplicate] Multilanguage page without formatting

score 1 · Accepted Answer

The trick is to make a query to save all possible values of the "Brand" column first:

  declare @cols nvarchar(max)

  set @cols = stuff((
        select distinct ',' + MARCA
        from Tabela_Produtos
        for xml path('')), 1,1, '');

and then mount the PIVOT in a dynamic query with EXECUTE:

  declare @query as nvarchar(max)

  set @query='SELECT * FROM (
     SELECT Preco, Marca
     FROM   Tabela_Produtos
  ) AS t
  PIVOT (
          SUM(Preco)
          FOR Marca IN ( ' + @cols + ' ) 
        ) as P;';

  execute(@query)

You can add other columns and GROUP BY as needed. Here is a well-explained, step-by-step tutorial: Dynamic Pivot with SQL