neste artigo, vamos percorrer os operadores de Pivô SQL e de Unpivot SQL e como eles podem ser úteis para transpor os dados do servidor SQL. Além disso, discutiremos formas estáticas e dinâmicas de usar operadores relacionais PIVOT e UNPIVOT que podem ser usados para transformar valores agregados distintos como coluna(s) no conjunto de resultados, especificando todos os valores da coluna no PIVOT na cláusula.
SQL Server pivot Introduction
There are several ways to transpose a dataset from rows to columns and columns to rows. Pivô SQL é uma das técnicas que permite a transposição de linhas para colunas e realiza possíveis agregações ao longo do caminho. Os operadores relacionais SQL PIVOT e SQL transpõem dados bidimensionais de valor de tabela para outra forma de dados. O pivô SQL transpõe uma expressão de valor de tabela a partir de um conjunto único de valores de uma coluna para múltiplas colunas na saída e realiza agregações. O UNPIVOT SQL executa a operação oposta do PIVOT SQL, transformando as colunas de uma expressão de valor de tabela em valores de coluna.
na maioria dos casos, a técnica de pivotagem estática é suficiente para o requisito de Negócio. Por exemplo, as previsões de vendas mensais, a repartição anual das vendas, a agregação trimestral das vendas, etc., em que as colunas da cláusula IN permanecem estáticas. Em alguns outros casos, precisamos de detalhes granulares e a expressão de valor de tabela é mais dinâmica na natureza e todo o tempo novo conjunto da expressão são incluídos na tabela então pivô dinâmico seria a melhor escolha.
Syntax
SELECT <non-pivoted column>,
AS <column alias>,
AS <column alias>,
…
AS <column alias>
FROM
(
<SELECT QUERY>)
AS <Alias for temporary data set>
PIVOT
(
<Aggregate function>( Aggregate column)
FOR
IN ( , ,
… )
) AS <tabela Dinâmica alias>
< cláusula ORDER BY>;
primeiros passos com o SQL Dinâmica
Vamos começar com o giro e onu-girando a dados. Agora você entende o que os operadores PIVOT e UNPIVOT são no servidor SQL. Em termos simples, é apenas outra maneira de descrever o processo de transformar valores de linha distintos em suas próprias colunas é conhecido como Pivoting. E o processo de transformar colunas em linhas é conhecido como um un-pivoting. exemplo 1: Como realizar uma operação pivô SQL
vamos imaginar o conjunto de dados de amostra derivado da base de dados AdventureWorks2014. Neste caso, você acabou de obter um conjunto de dados simples com a coluna do Ano comercial ao longo do único valor quantitativo TotalSales.
Agora, o conjunto de dados está pronto para rodar. Tens ano de vendas na primeira coluna, TotalSales na segunda coluna. Se você pediu para transpor os dados, você tem que tomar cada ano distinto na coluna e estas colunas se tornam o cabeçalho das colunas pivot. Assim, 2011 torna-se a primeira coluna, 2012 torna-se a sua própria coluna e assim por diante. Os valores reais da coluna seguinte Totalsales, eles ficam com os cabeçalhos da coluna pivot. Agora, a tabela giratória pareceria abaixo.
|
1
2
3
4
5
6
7
8
9
10
|
SELECT * FROM
(
SELECT YEAR(SOH.OrderDate) as SalesYear,
SOH.SubTotal como TotalSales
das vendas.SalesOrderHeader SOH
JOIN sales.SalesOrderDetail SOD ON SOH.SalesOrderId = SOD.SalesOrderId
) COMO de Vendas
PIVOT (SOMA(TotalSales)
PARA SalesYear EM (,,,))
como PVT
|
a imagem A seguir mostra os dados de entrada, Dinâmica de sintaxe, SQL Dinâmica, e a saída de resultados.
Exemplo 2: Como executar uma operação Unpivot SQL
vamos agora olhar para a outra direção, un-pivoting, que é tão simples como ir da horizontal para vertical. Neste caso, você pegaria todas aquelas colunas distintas que são selecionadas, e transformaria os cabeçalhos das colunas em suas próprias linhas. Pivotando é como rodar de vertical para horizontal. E un-pivoting é como rodar de horizontal para vertical.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
SELECIONE SalesYear,
TotalSales
a PARTIR do
(
SELECT *
a PARTIR do
(
SELECT YEAR(SOH.OrderDate) AS SalesYear,
SOH.SubTotal como TotalSales
das vendas.SalesOrderHeader SOH
JOIN sales.SalesOrderDetail SOD ON SOH.SalesOrderId = SOD.SalesOrderId
) COMO de Vendas DINÂMICA(SOMA(TotalSales) PARA SalesYear EM(,
), COMO PVT
) T UNPIVOT(TotalSales PARA SalesYear EM(,
), COMO upvt;
|
No exemplo a seguir, o articulado conjunto de dados é usado un-dinâmica dos valores. A peça chave aqui é que isso foi fácil de fazer em se transformar porque fomos capazes de criar uma tabela de pivô como um passo intermediário e, em seguida, fazer a transformação usando a operação unpivot.
Nota: o SQL dinâmica dos resultados da operação na transposição de agregados resultado na coluna, mas enquanto o SQL unpivot não é uma exata inversão da transposição de colunas para o segregados os valores das linhas. O operador de unpivot não irá dividir os resultados agregados.exemplo 3: Como obter vendas trimestrais usando a operação pivot SQL
vamos ver mais alguns exemplos para entender melhor os conceitos pivotantes. Neste caso, vamos ver como obter dados trimestrais de vendas agregadas com base nas vendas trimestrais.
Antes de saltar para a solução, é sempre recomendado trabalhar na criação de um conjunto de dados para a operação pivô.o exemplo seguinte apresenta a distribuição mensal agregada das vendas ao longo do ano de vendas.
|
1
2
3
4
5
6
7
8
9
|
SELECT YEAR(SOH.OrderDate) como Ano de vendas,
mês(SOH.OrderDate) como SalesMonth,
SUM(SOH.SubTotal) como TotalSales
das vendas.SalesOrderHeader SOH
JOIN sales.SalesOrderDetail SOD ON SOH.SalesOrderId = SOD.SalesOrderId
grupo por ano(SOH.OrderDate),
mês(SOH.OrderDate)
ordem por ano(SOH.OrderDate),
mês(SOH.Datadopedido);
|
O seguinte resultado mostra a base de dados
uma Vez que você está pronto com a base de dados, você pode aplicar a DINÂMICA do operador. No exemplo a seguir, a cláusula IN aceita um caráter não-numérico que foi gerado com base no valor DATEPART. O valor do DATEPART é concatenado com ” Q ” e então os valores são alimentados na cláusula do operador PIVOT.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
SELECIONE SalesYear,
ISNULL(, 0) Q1,
ISNULL(, 0), P2,
ISNULL(, 0), P3,
ISNULL(, 0) COMO P4,
(ISNULL(, 0) + ISNULL(, 0) + ISNULL(, 0) + ISNULL(, 0)) SalesYTD
DE
(
SELECT YEAR(SOH.OrderDate) AS SalesYear,
CAST(‘Q’+CAST(DATEPART(QUARTER, SOH.OrderDate) AS VARCHAR(1) AS VARCHAR (2) Quarters,
SOH.SubTotal como TotalSales
das vendas.SalesOrderHeader SOH
JOIN sales.SalesOrderDetail SOD ON SOH.SalesOrderId = SOD.SalesOrderId
) como PIVOT de dados(soma (TotalSales) para quartos em (,
,
,
) como pvt
;
|
A saída a seguir é um trimestrais de divisão dos dados de vendas
Exemplo 4: Como obter mensal de vendas usando um SQL dinâmica de operação
Vamos dar uma olhada em outro exemplo, para ver as vendas de divisão de dados com base em cada mês. No exemplo seguinte, os valores da cláusula IN são gerados como parte da função DATEPART. Os valores do DATEPART são alimentados na cláusula do operador PIVOT.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
|
SELECIONE SalesYear,
Isnull(, 0) como Jan,
Isnull(, 0), Fev,
Isnull(, 0) como Mar,
ISNULL(, 0), Abr
isnull(, 0), como Pode,
isnull(, 0), Jun,
isnull(, 0), Jul,
isnull(, 0), Ago,
isnull(, 0) como Set,
ISNULL(, 0) como Oct,
isnull(, 0), Nov,
isnull(, 0) como Dec,
(isnull(, 0) + isnull(, 0) + ISNULL(, 0) + isnull(, 0) + isnull(, 0) + isnull(, 0) + ISNULL(, 0) + ISNULL(, 0) + isnull(, 0) + isnull(, 0) +isnull (, 0) + isnull (, 0) + isnull (, 0) + ISNULL (, 0) + ISNULL (, 0) + ISNULL (, 0)) SalesYTD
da
(
SELECT YEAR(SOH.OrderDate) AS SalesYear,
DATEPART(MONTH, SOH.Data) meses,
SOH.SubTotal como TotalSales
das vendas.SalesOrderHeader SOH
JOIN sales.SalesOrderDetail SOD ON SOH.SalesOrderId = SOD.SalesOrderId
) COMO Dados de PIVÔ(SOMA(TotalSales) PARA Meses(,
,
), COMO pvt;
|
A saída a seguir é um mensais de divisão dos dados de vendas
Exemplo 5: Como obter mensal de vendas, com o uso de SQL dinâmica dinâmica de operação
Vamos endereço mensal de divisão de dados de vendas utilizando dinâmicas de giro. Até agora, discutimos operações de rotação estática. A fim de converter uma operação pivô estático para dinâmico, temos que remover os valores hardcoded da cláusula IN. Primeiro, obter a expressão distinta do conjunto de dados e, em seguida, preparar uma string, concatenando todas as expressões. No exemplo seguinte, o @columnname é usado para concatenar toda a expressão. A expressão concatenada é alimentada para pivot em cláusula. O resto é uma conversão simples de SQL estático em um SQL dinâmico e chamar @DML usando o procedimento de armazenamento sp_executesql.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
INFORMANDO @dml COMO tipo de dados NVARCHAR(MAX)
DECLARADO @ColumnName AS NVARCAR (MAX)
SELECT @ColumnName= ISNULL (@ColumnName + ‘,’,”)
+ QUOTENAME (Months)
from(SELECT DISTINCT DATEPART (MONTH, SOH.Data) meses
das vendas.SalesOrderHeader SOH
JOIN sales.SalesOrderDetail SOD ON SOH.SalesOrderId = SOD.SalesOrderId
grupo por ano(SOH.OrderDate),
DATEPART(MONTH, SOH.Datadopedido)) COMO Meses
–Prepare a DINÂMICA de consulta usando a dinâmica
SET @dml =
N SELECT SalesYear, ‘+@ColumnName + ‘ DO
(
SELECT YEAR(SOH.OrderDate) AS SalesYear,
DATEPART(MONTH, SOH.Data) meses,
soma(SOH.SubTotal) como TotalSales
das vendas.SalesOrderHeader SOH
JOIN sales.SalesOrderDetail SOD ON SOH.SalesOrderId = SOD.SalesOrderId
grupo por ano(SOH.OrderDate),
DATEPART(MONTH, SOH.Datadopedido)) COMO T
PIVOT(SOMA(TotalSales)
POR Meses NA (‘ + @ColumnName + ‘)) COMO PVTTable’
–Print @DynamicPivotQuery
–Executar a dinâmica Dinâmica de Consulta
EXEC sp_executesql @dml
|
Isso é tudo por agora…
Quebra automática
até agora, demonstramos os conceitos básicos e vários exemplos de SQL DINÂMICA e SQL UNPIVOT.
a amostra é baseada na base de dados SQL Server adventureworks2014. Pode tentar testar todas as amostras sozinho. Se você tiver qualquer pergunta, por favor, sinta-se livre para comentar abaixo…
- Autor
- Posts Recentes
minha especialidade está em projetar & implementando soluções de alta disponibilidade e migração de DB entre plataformas. As tecnologias atualmente em funcionamento são SQL Server, PowerShell, Oracle e MongoDB.
Ver todos os posts por Prashanth Jayaram
- Uma rápida visão geral de auditoria de banco de dados em SQL – janeiro 28, 2021
- Como configurar Azure Sincronização de Dados entre bancos de dados SQL Azure e local do SQL Server – 20 de janeiro de 2021
- Como realizar Azure SQL database operações de Importação/Exportação usando o PowerShell – 14 de janeiro de 2021