SQL Procedure improvement

Asked

Viewed 79 times

1

I wonder if there was a way to improve a code I made on hand in SQL... I have a table from 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 row 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 is correct ! but in this case I wrote 3 brands (bk, Kfc and mc) if I had 500 brands I would have to write in my hand. My question would be, would there be a way to improve this by making the idependente of 1,5,10 thousand records I run a certain code/process that makes it ready ? (I apologize if I haven’t been clear enough)

sql-server
  • 1

    https://technet.microsoft.com/en-us/library/ms177410(v=sql.105). aspx

  • What you need is to mount a dynamic PIVOT, transforming each "Mark" column value into new columns. The trick is to make a query to store all possible values of the "Tag" column and then mount the PIVOT in a dynamic query with EXECUTE. Here is a very didactic step by step: http://vladimirrech.blogspot.com.br/2014/02/pivot-dinamico-com-sql-server.html

  • @Vanderleiadriano but in case I won’t have to inform all the marks in the pivot ? if I have 1000 records I will put IN([bk],[mc]...) ??

1 answer

1


The trick is to make a query to save all possible values from the "Tag" 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 an explanation and step by step well didactic: Dynamic Pivot with SQL

Browser other questions tagged sql-server

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