Introdução
No meu último artigo, “a Conversão de valores Separados por Vírgula para Linhas e Vice-Versa no SQL Server”, eu falei sobre como você pode converter separados por vírgulas (a) ou separado (com alguns outros caracteres) valores em uma única coluna em linhas e vice-versa. Neste artigo, eu demonstro como você pode converter valores de linhas em valores de colunas (pivô) e valores de colunas em valores de linhas (UNPIVOT) no servidor SQL.,
Converter linhas para colunas – pivô
SQL Servidor tem um operador relacional pivô para transformar os valores únicos de uma coluna especificada de várias linhas em vários valores de coluna na saída (tabulação cruzada), girando efetivamente uma tabela. Ele também permite realizar agregações, sempre que necessário, para valores de coluna que são esperados na saída final. A sintaxe básica para um operador relacional PIVOT se parece com esta:
com o script abaixo, vamos criar uma tabela e carregar alguns dados para ela., Como você pode ver na imagem abaixo, ele tem informações de vendas para alguns países por um par de anos. Além disso, se você notar, para cada país e para cada ano há uma linha separada.
agora, usando o operador PIVOT, vamos converter valores de linhas em valores de coluna com o script dado abaixo e os resultados como mostrado na imagem abaixo.
embora tenhamos usado a função de agregação soma, neste caso não há soma, pois há apenas uma linha para cada combinação única para o país e ano., Por favor, note a utilização de parêntesis para os valores da coluna pivot; estes são necessários.
trazendo dinamismo ao PIVOT
Se você notar no script acima, nós fornecemos valores (2005, 2006, 2007 e 2008) para colunas pivot como estes valores estão disponíveis nos conjuntos de dados originais. Mas e se alguns valores adicionais forem esperados no futuro, por exemplo 2009 e 2010, etc. para a coluna pivot?,
nesse caso, você ainda pode usar os valores da coluna pivot, que são esperados para vir (ou que ainda não estão disponíveis no conjunto de dados original) no futuro, embora você verá NULL para seus valores. O script abaixo mostra este cenário e a imagem abaixo mostra NULLs para os anos 2009 e 2010, uma vez que não há dados para esses anos.
a abordagem acima discutida funciona bem se você já conhece todos os valores possíveis para a coluna pivot, mas e se você não souber?,
nesse caso, você pode escrever uma consulta dinâmica, primeiro, pegue todos os valores para a coluna de perfuração em tempo de execução e, em seguida, uma escrita dinâmica de consulta para executá-lo com a dinâmica de consulta, como mostrado abaixo:
a Conversão de Colunas para Linhas – UNPIVOT
UNPIVOT é outro operador relacional do SQL Server que executa quase a operação inversa de PIVÔ, girando valores de coluna em linhas de valores. Deixe-me demonstrar isto com um exemplo; vamos criar uma tabela com dados pivotados da consulta anterior com o script abaixo., A imagem abaixo mostra dados da tabela recém-criada com dados pivotados.
para rodar os valores da coluna em valores de linha, agora vamos usar o operador de UNPIVOT como mostrado abaixo. A imagem abaixo mostra dados rodados:
Se você se referir de volta, eu disse que UNPIVOT é quase o reverso do operador PIVOT; isso significa que ele pode ou não ser exatamente o mesmo. A razão é que Pivot realiza agregação enquanto roda os valores de linha em valores de coluna e pode juntar possíveis múltiplos valores de linha em um único valor de coluna na saída., Por exemplo, considere para um determinado país e ano existem dois valores, digamos 5000 e 6000. Agora, quando você o Roda, a saída terá 11000 como o valor da coluna se você tiver soma como a função de agregação. Mais tarde, se você quiser desempivot de volta, você receberá 11000 não os valores bi-furcados (5000 e 6000 como original). Dito isto, podemos dizer que se os valores pivotados são valores agregados, não será possível obter os dados originais de volta.,
conclusão
neste artigo, eu demonstrei como você pode converter valores de linhas em valores de coluna (PIVOT) e valores de coluna em valores de linha (UNPIVOT) no servidor SQL. Eu também falei sobre escrever uma consulta dinâmica para escrever uma consulta dinâmica pivot em que os valores possíveis coluna pivot são determinados no tempo de execução.