PIVOT no SQL Server parte II

Olá Caríssimos tudo bem ?

No post anterior falei sobre como fazer um PIVOT simples no SQL Server. (veja aqui)

Só uma recaptulação rápida: Utilizamos o PIVOT quando queremos mostrar linhas como colunas (transpor) em uma tabela.

No nosso exemplo tínhamos uma tabela de Vendas como o Layout :

E fizemos a query para mostrar a tabela assim:

Colocamos todas as cidades como Colunas.

Fizemos a query de uma forma simples, adicionando ‘hard code  o nome das cidades.

Select Marca,[São Paulo],[Rio de Janeiro],[Salvador],[Tarabai],[Campina Grande]
 from
 (
 Select Marca,Cidade,TotalVendido from vendas
 ) PT

PIVOT
 ( SUM(TotalVendido) for Cidade in ([São Paulo],[Rio de Janeiro],[Salvador],[Tarabai],[Campina Grande])) pvt

No fim do post levantei a questão se caso os itens que queremos mostrar como Colunas, neste caso cidades, fossem dinâmicas e como ficaria a consulta. Isso seria bem útil também para datas, já que é fora de questão ficarmos colocando datas que sempre mudam diretamente na query.

Quando digo dinâmicas, quero dizer que não sabemos o que virá como dado e que estes dados podem variar com o tempo, filtro ou qualquer outra situação.

No caso de dados dinâmicos temos que fazer alguns ajustes na query e torná-la dinâmica também:

Passo 1: Separar os dados que serão colunas

Vamos selecionar os valores que se tornarão colunas (no nosso caso eles estão na mesma tabela, mas não faz diferença se estiver em outra tabela relacionada) já no formato que queremos.

declare @values as nvarchar(max)
SELECT  @values = STUFF(( SELECT DISTINCT
'],[' + cidade
FROM    Vendas
ORDER BY '],[' + cidade
FOR XML PATH('')
), 1, 2, '') + ']'

Esse código irá gerar algo do tipo:


[Campina Grande],[Rio de Janeiro],[Salvador],[São Paulo],[Tarabai]

Passo 2: Preparar o esqueleto da query

Agora vamos criar o esqueleto da consulta final:


declare @query as nvarchar(max)
set @query = 'Select Marca, |@
from
(
Select Marca,Cidade,TotalVendido from vendas
) PT

PIVOT
( SUM(TotalVendido) for Cidade in ( |@ )) pvt'

OBS: Na query acima coloquei o marcador ‘|@’, que será substituído pelos valores das cidades.

Passo 3: Substituir os marcadores pelos valores obtidos no passo 1, executar a query e correr pro abraço!!!!!!!

set @query = REPLACE(@query,'|@',@values)
exec(@query)

Nosso código completo:

--- Separando os valores: Passo 1
declare @values as nvarchar(max)
SELECT  @values = STUFF(( SELECT DISTINCT
'],[' + cidade
FROM    Vendas
ORDER BY '],[' + cidade
FOR XML PATH('')
), 1, 2, '') + ']'

--- Esqueleto da query com os marcadores: Passo 2
declare @query as nvarchar(max)
set @query = 'Select Marca, |@
from
(
Select Marca,Cidade,TotalVendido from vendas
) PT

PIVOT
( SUM(TotalVendido) for Cidade in ( |@ )) pvt'
set @query = REPLACE(@query,'|@',@values)
--- Executando a query
exec(@query)

É isso, está feito.
Não é o melhor dos mundos esta solução mas não há outra forma.

Abraço a todos!!

Anúncios

, , , , , , , ,

  1. #1 por Paulo Diogo em 8 de outubro de 2013 - 8:34 am

    Muito bom manoo! salvou meu dia!

  2. #2 por Marcos Felipe em 11 de dezembro de 2013 - 11:27 am

    Cara o meu não deu certo.. Tenho os campos
    description valor workhour
    MOTOR DIESEL M1 1870
    MOTOR DIESEL M2 0
    MOTOR DIESEL M3 0
    TRANSMISSÃO M1 16640
    TRANSMISSÃO M2 0
    TRANSMISSÃO M3 0

    Quero Transformar description em coluna, valor em linha e workhour em dados.. tipo isso
    MOTOR DIESEL TRANSMISSÃO
    M1 1870 16640
    M2 0 0
    M3 0 0

    Fiz o seguinte codigo:

    Declare @values as nvarchar(max)
    Select @values = STUFF((Select Distinct ‘],[‘ + description From #ChartComponents Order By ‘],[‘ + description FOR XML PATH(”)),1,2,”) + ‘]’
    Set @values = REPLACE(@values,’ ‘,’_’)
    Declare @query as nvarchar(max)
    Set @query =’Select valor,|@ From (Select description,valor,workhour From #ChartComponents) PT PIVOT (sum(workhour) For description in (|@)) pvt Group By valor’
    Set @query = REPLACE(@query,’|@’,@values)
    exec @query

  3. #3 por Marcos Felipe em 11 de dezembro de 2013 - 11:33 am

    o erro gerado acima foi o seguinte..
    Mensagem 203, Nível 16, Estado 2, Linha 35
    O nome ‘Select valor,[MOTOR_DIESEL],[TRANSMISSÃO] From (Select description,valor,workhour From #ChartComponents) PT PIVOT (sum(workhour) For description in ([MOTOR_DIESEL],[TRANSMISSÃO])) pvt’ não é um identificador válido.

  4. #4 por Roberto em 27 de dezembro de 2013 - 3:45 pm

    Estou com um problema ao trabalhar com datas como coluna.

    declare @values as nvarchar(max)
    SELECT @values = STUFF(( SELECT DISTINCT
    #],[# + data
    FROM Vendas
    ORDER BY #],[# + data
    FOR XML PATH(”)
    ), 1, 2, ”) + #]#

    onde eu errei?

  5. #5 por Roberto em 27 de dezembro de 2013 - 3:51 pm

    Código completo … sou bem junior nisso, mas ando tendo sucesso. Queria saber onde eu errei ou deixei de referenciar que a coluna é uma data.

    — Separando os valores: Passo 1
    declare @values as nvarchar(max)
    SELECT @values = STUFF(( SELECT DISTINCT
    #],[# + [refdate]
    FROM jdt1
    ORDER BY #],[# + [refdate]
    FOR XML PATH(”)
    ), 1, 2, ”) + ‘]’

    — Esqueleto da query com os marcadores: Passo 2
    declare @query as nvarchar(max)
    set @query = ‘Select [ShortName], |@
    from
    (
    Select [refdate],[ShortName],[Credit] from jdt1
    ) PT

    PIVOT
    ( SUM([Credit]) for [refdate] in ( |@ )) pvt’
    set @query = REPLACE(@query,’|@’,@values)
    — Executando a query
    exec(@query)

    • #6 por Délio Tomaz em 31 de dezembro de 2013 - 6:08 pm

      Roberto, você tem que dar o Cast no Select externo, onde voce precisa apresentar os resultados.
      abs

  6. #7 por Nathália Lage em 4 de agosto de 2014 - 4:28 pm

    Muito bom, ajudou demais ! 🙂

  7. #8 por Debora em 23 de setembro de 2016 - 5:01 pm

    Muito bom ajudou muito, só uma duvida. como que eu faço para no lugar da soma eu colocar um texto. Pois na minha tabela eu queria algo assim:

    Pessoas| Questao 1 | Questao 2| Questao 3 …
    Pessoa1| Resposta1|Resposta2 |Resposta3 …
    Pessoa2| Resposta1|Resposta2 |Resposta3 …

    só que eu não sei quantas questões tem no banco. na tabela esta assim:

    cod_pessoa|questao|resposta
    1 |q1 | r1
    1 |q2 | r2
    2 |q1 | r1
    2 |q2 | r2

    Obrigada

    • #9 por Anônimo em 23 de setembro de 2016 - 7:26 pm

      Ola Debora.
      Uma vez fiz com texto, se não me engano usei a função agregada MAX na coluna texto.
      Acho que funciona

      abs

      • #10 por Debora em 29 de setembro de 2016 - 11:01 am

        Muito obrigada funcionou mesmo, salvou meu dia e meu mês rsrsrs fiquei até feliz kkk pensei que não conseguiria fazer tão cedo.

  8. #11 por Anônimo em 23 de setembro de 2016 - 7:26 pm

    Ola Debora.
    Uma vez fiz com texto, se não me engano usei a função agregada MAX na coluna texto.
    Acho que funciona

    abs

  9. #12 por Debora em 29 de setembro de 2016 - 10:42 am

    Obrigada vou tentar 😉

    • #13 por Debora em 29 de setembro de 2016 - 11:28 am

      Muito obrigada funcionou mesmo, salvou meu dia e meu mês rsrsrs fiquei até feliz kkk pensei que não conseguiria fazer tão cedo.

  1. PIVOT no SQL Server parte I « debulhado…

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s

%d blogueiros gostam disto: