Resumo: neste tutorial, você aprenderá a usar o operador PIVOT do SQL Server para converter linhas em colunas.
Configurando as metas
Para a demonstração, usaremos production.products e production.categories tabelas do banco de dados de exemplo:
A consulta a seguir encontra o número de produtos para cada categoria de produto:
Aqui está a saída:
Nosso objetivo é transformar os nomes das categorias da primeira coluna da saída em várias colunas e contar o número de produtos para cada uma nome da categoria como a seguinte imagem:
Além disso, podemos adicionar o ano do modelo para agrupar a categoria ory por ano do modelo, conforme mostrado na seguinte saída:
Introdução ao operador PIVOT do SQL Server
O operador PIVOT do SQL Server gira uma expressão com valor de tabela. Ele transforma os valores exclusivos em uma coluna em várias colunas na saída e realiza agregações em quaisquer valores de coluna restantes.
Você segue estas etapas para tornar uma consulta uma tabela dinâmica:
- Primeiro, selecione um conjunto de dados base para dinamização.
- Segundo, crie um resultado temporário usando uma tabela derivada ou expressão de tabela comum (CTE)
- Terceiro, aplique o
PIVOT.
Vamos aplicar essas etapas no exemplo a seguir.
Primeiro, selecione o nome da categoria e o id do produto em production.products e production.categories tabelas como dados básicos para dinamização:
Em segundo lugar, crie um conjunto de resultados temporário usando uma tabela derivada:
Terceiro, aplique o operador PIVOT:
Esta consulta gera a seguinte saída:
Agora, qualquer coluna adicional que você adicionar à lista de seleção da consulta que retorna o base os dados formarão grupos de linhas automaticamente na tabela dinâmica. Por exemplo, você pode adicionar a coluna do ano do modelo à consulta acima:
Aqui está a saída:
Gerando valores de coluna
Na consulta acima, você tinha que digitar cada nome de categoria entre parênteses após o IN operador manualmente. Para evitar isso, você pode usar a função QUOTENAME() para gerar a lista de nomes de categorias e copiá-los sobre a consulta.
Primeiro, gere a lista de nomes de categorias:
A saída será semelhante a esta:
Neste trecho:
- A função
QUOTENAME()envolve o nome da categoria pelos colchetes, por exemplo, - A função
LEFT()remove a última vírgula da string @columns .
Em segundo lugar, copie a lista de nomes de categoria da saída e cole-a na consulta.
Tabelas dinâmicas dinâmicas
Se você adicionar um novo nome de categoria para a tabela production.categories, você precisa reescrever sua consulta, o que não é ideal. Para evitar isso, você pode usar SQL dinâmico para tornar a tabela dinâmica dinâmica.
Nesta consulta, em vez de passar uma lista fixa de nomes de categorias para o PIVOT operador, construímos a lista de nomes de categoria e a passamos para uma instrução SQL e, em seguida, executamos essa instrução dinamicamente usando o procedimento armazenado sp_executesql.
Neste tutorial , você aprendeu a usar a tabela PIVOT do SQL Server para converter linhas em colunas.