SQL Pivot - Saiba como converter linhas em colunas

Relacional armazenar enormes quantidades de dados na forma de tabelas. Essas tabelas podem ter qualquer número de linhas e colunas. Mas, e se você tivesse que alterar os dados de nível de linha para dados colunares? Bem, neste artigo sobre o SQL Pivot, vou mostrar como você pode converter linhas em uma coluna em um SQL Server.

Os seguintes tópicos serão abordados neste artigo:

• O que é PIVOT no SQL?
• Sintaxe
• Exemplo
• Trabalho da cláusula PIVOT
• SQL UNPIVOT

O que é PIVOT no SQL?

PIVOT é usado para girar o valor da tabela, convertendo os valores exclusivos de uma única coluna em várias colunas. É usado para girar as linhas para valores de coluna e executa agregações quando necessário nos valores de coluna restantes.

O UNPIVOT, por outro lado, é usado para realizar as operações opostas. Portanto, ele é usado para converter as colunas de uma tabela específica em valores de coluna.

Continuando neste artigo, vamos entender a sintaxe do SQL Pivot.

Sintaxe:

SELECT NonPivoted ColumnName, [First Pivoted ColumnName] AS ColumnName, [Second Pivoted ColumnName] AS ColumnName, [Third Pivoted ColumnName] AS ColumnName, ... [Last Pivoted ColumnName] AS ColumnName FROM (SELECT query que produz os dados) AS [alias para a consulta inicial] PIVOT ([AggregationFunction] (ColumName) FOR [ColumnName da coluna cujos valores se tornarão cabeçalhos de coluna] IN ([First Pivoted ColumnName], [Second Pivoted ColumnName], [Third Pivoted ColumnName] ... [last coluna dinâmica])) AS [alias para a tabela dinâmica]

Aqui,você também pode usar o Cláusula ORDER BY para classificar os valores em ordem crescente ou decrescente. Agora que você sabe o que é PIVOT em SQL e sua sintaxe básica, vamos seguir em frente e ver como usá-lo.

java cast duplo para int

Exemplos

Para sua melhor compreensão, considerarei a tabela a seguir para explicar todos os exemplos.

Tabela de fornecedores:

Vamos escrever uma consulta simples para recuperar o custo médio gasto por cada cliente.

SELECIONE CustomerID, AVG (Custo) AS AverageCostofCustomer FROM Fornecedores GROUP BY CustomerID

Resultado:

Agora, digamos que queremos dinamizar a tabela acima. Aqui, os valores da coluna CustomerID se tornarão os cabeçalhos das colunas.

- Criar tabela dinâmica com uma linha e três colunas SELECT 'AverageCostofCustomer' AS Cost_According_To_Customers, [11], [22], [33] FROM (SELECT CustomerID, Cost FROM Supplier) AS SourceTable PIVOT (AVG (Cost) FOR CustomerID IN ( [11], [22], [33])) AS Tabela Dinâmica

Resultado:

Nota: Quando você usa funções agregadas com PIVOT, os valores nulos não são considerados ao calcular uma agregação.

Bem, esse foi um exemplo básico, mas vamos agora entender como funcionava a cláusula PIVOT.

Trabalho da cláusula PIVOT

Como você pode consultar acima, para criar uma TABELA PIVOT, você precisa seguir as etapas abaixo:

• Selecione colunas para dinamizar
• Em seguida, selecione uma tabela de origem.
• Aplique o operador PIVOT e, em seguida, use as funções de agregação.
• Mencione os valores de pivô.

Selecione colunas para dinamizar

Inicialmente, temos que especificar os campos a serem incluídos em nossos resultados. Em nosso exemplo, considerei a coluna AverageCostofCustomer na tabela dinâmica. Em seguida, criamos três outras colunas com os cabeçalhos de coluna 11, 22 e 33. Exemplo-

SELECIONE 'AverageCostofCustomer' AS Cost_According_To_Customers, [11], [22], [33]

Selecione a tabela de origem

Em seguida, você deve especificar a instrução SELECT que retornará os dados de origem para a tabela dinâmica. Em nosso exemplo, estamos retornando o CustomerID e o Custo da tabela Fornecedores.

(SELECIONE ID do cliente, Custo dos fornecedores) AS SourceTable

Aplique o operador PIVOT e, em seguida, use as funções de agregação

Em seguida, você deve especificar a função de agregação a ser usada ao criar a tabela dinâmica. Em nosso exemplo, usei a função AVG para calcular o custo médio.

PIVOT (AVG (custo)

Mencione os valores dinâmicos

Finalmente, você deve mencionar os valores que devem ser incluídos na tabela dinâmica resultante. Esses valores serão usados ​​como cabeçalhos de coluna na tabela dinâmica.

PARA CustomerID IN ([11], [22], [33])) COMO Tabela Dinâmica

É assim que os operadores PIVOT funcionam. Continuando neste artigo sobre SQL PIVOT, vamos entender como ele é diferente do SQL UNPIVOT.

SQL UNPIVOT

O operador SQL UNPIVOT é usado para realizar a operação oposta à de PIVOT. É usado para girar os dados da coluna em dados de nível de linha. A sintaxe de UNPIVOT é semelhante à de PIVOT. A única diferença é que você deve usar o ' UNPIVOT ” .

Exemplo:

Vamos criar uma tabela com as colunas SupplierID, AAA, BBB e CCC. Além disso, insira alguns valores.

CREATE TABLE sampletable (SupplierID int, AAA int, BBB int, CCC int) IR INSERT INTO sampletable VALUES (1,3,5,6) INSERT INTO sampletable VALUES (2,9,2,8) INSERT INTO sampletable VALUES (3, 8,1,7) GO

Resultado:

Agora, digamos, queremos remover o pivô da mesa. Para fazer isso, você pode consultar o seguinte código:

SELECIONE ID do fornecedor, clientes, produtos DE (SELECIONE fornecedorD, AAA, BBB, CCC DE amostra da tabela) p UNPIVOT (Produtos PARA Clientes IN (AAA, BBB, CCC)) Como exemplo GO

É assim que você pode usar o SQL PIVOT e UNPIVOT. Com isso, chegamos ao fim deste artigo. Espero que você tenha entendido como usar SQL. Se você deseja aprender mais sobre MySQL e conheça este banco de dados relacional de código aberto e, em seguida, verifique nosso que vem com treinamento ao vivo conduzido por instrutor e experiência em projetos da vida real. Este treinamento o ajudará a entender o MySQL em profundidade e a obter domínio sobre o assunto.

Tem alguma questão para nós? Mencione isso na seção de comentários deste artigo sobre o SQL Pivot e entrarei em contato com você.