Pivoteando, Despivoteando, Transpondo, Invertendo Colunas e Linhas no SQL Server

Boa Tarde,

Hoje pela manhã em um dos clientes que presto consultoria, me deparei com um desafio bem interessante. Consistia em transformar as linhas de uma determinada consulta em colunas e transformar as colunas dessa mesma consulta em linhas. Foi sem dúvida algo bem novo, pois, nunca tinha me deparado com tal situação. Normalmente, iria pensar em algum código TSQL bem complexo com instruções SQL dinâmicas ou quem sabe até cursorzinho no final das contas. Bem, pensando um pouco, a solução apareceu de uma forma bem mais simples. Vejamos o caso prático:

— Cria uma tabela de relatório de vendas de 2008
— Possivelmente obtida através de uma consulta ou processo de ETL

CREATE TABLE RelatorioVendas2008 (
    [Categoria] VARCHAR(20),
    [Jan] MONEY, [Fev] MONEY, [Mar] MONEY, [Abr] MONEY, [Mai] MONEY, [Jun] MONEY,
    [Jul] MONEY, [Ago] MONEY, [Set] MONEY, [Out] MONEY, [Nov] MONEY, [Dez] MONEY)

— Insere alguns registros de Vendas
INSERT INTO RelatorioVendas2008 VALUES (‘Eletrônicos’,
    20396.13, 12430.55, 15467.89, 17536.90, 18370.32, 17960.41,
    16435.04, 13430.87, 12420.63, 14870.12, 19270.53, 24568.09)

INSERT INTO RelatorioVendas2008 VALUES (‘Vestiário’,
    70453.60, 58370.59, 56230.33, 60237.20, 63547.25, 68236.97,
    62536.88, 57243.90, 55243.09, 58210.03, 65497.80, 75889.23)

INSERT INTO RelatorioVendas2008 VALUES (‘Alimentos’,
    14097.33, 10054.56, 17240.72, 20540.14, 21042.99, 22340.22,
    20760.28, 20870.15, 19903.27, 20432.84, 22456.00, 25378.19)

INSERT INTO RelatorioVendas2008 VALUES (‘Medicamentos’,
    4502.56, 3200.18, 5607.31, 6230.89, 6457.63, 6241.55,
    6117.74, 5949.52, 6303.22, 6177.84, 6098.16, 6559.26)

Uma consulta sobre a tabela produz o resultado da tabela abaixo (resultados simplificados):

Categoria Jan Fev Mar Abr Mai Jun
Eletrônicos 20.396,13 12.430,55 15.467,89 17.536,90 18.370,32 17.960,41
Vestiário 70.453,60 58.370,59 56.230,33 60.237,20 63.547,25 68.236,97
Alimentos 14.097,33 10.054,56 17.240,72 20.540,14 21.042,99 22.340,22
Medicamentos 4.502,56 3.200,18 5.607,31 6.230,89 6.457,63 6.241,55

E qual seria o comando para produzir o seguinte resultado ?

Mês Eletrônicos Vestiário Alimentos Medicamentos
Jan 20.396,13 70.453,60 14.097,33 4.502,56
Fev 12.430,55 58.370,59 10.054,56 3.200,18
Mar 15.467,89 56.230,33 17.240,72 5.607,31
Abr 17.536,90 60.237,20 20.540,14 6.230,89
Mai 18.370,32 63.547,25 21.042,99 6.457,63
Jun 17.960,41 68.236,97 22.340,22 6.241,55
Jul 16.435,04 62.536,88 20.760,28 6.117,74
Ago 13.430,87 57.243,90 20.870,15 5.949,52
Set 12.420,63 55.243,09 19.903,27 6.303,22
Out 14.870,12 58.210,03 20.432,84 6.177,84
Nov 19.270,53 65.497,80 22.456,00 6.098,16
Dez 24.568,09 75.889,23 25.378,19 6.559,26

A SQL é realmente poderosa na recuperação e manipulação de dados, mas não é perrogativa dela disponibilizar mecanismos mais avançados de formatação e disposição dos dados. De fato, não há nada na SQL (e em nenhuma extensão proprietária que eu conheça) uma funcionalidade como essa (bem característica do recurso "Transpor" do Excel). Ainda assim, dada algumas limitações, é possível improvisar.

A tabela RelatorioVendas2008 é uma matriz entre categorias de produtos e os meses em que eles foram vendidos. As categorias de produto representam as linhas da matriz enquanto os meses representam as colunas. Os valores vendidos representam a interseção entre uma dada categoria e um mês específico em que ela foi vendida. Independente da disposição em linhas em colunas, o valor continuará representando essa interseção entre uma certa categoria e um mês e qualquer solução deve preservar essa característica. O comando abaixo "desvipoteia" a tabela RelatorioVendas2008 exibindo as categorias, os meses e os valores em linhas.

SELECT [Categoria], [Mes], [Valor]
FROM
   (SELECT [Categoria],
    [Jan], [Fev], [Mar], [Abr], [Mai], [Jun],
    [Jul], [Ago], [Set], [Out], [Nov], [Dez]
   FROM RelatorioVendas2008) As P
UNPIVOT
   ([Valor] FOR [Mes] IN (
        [Jan], [Fev], [Mar], [Abr], [Mai], [Jun],
        [Jul], [Ago], [Set], [Out], [Nov], [Dez])
) As UP

O resultado é exibido conforme a tabela abaixo (resultados simplificados):

Categoria Mês Valor
Eletrônicos Jan 20.396,13
Eletrônicos Fev 12.430,55
Eletrônicos Mar 15.467,89
Vestiário Jan 70.453,60
Vestiário Fev 58.370,59
Vestiário Mar 56.230,33
Alimentos Jan 14.097,33
Alimentos Fev 10.054,56
Alimentos Mar 17.240,72
Medicamentos Jan 4.502,56
Medicamentos Fev 3.200,18
Medicamentos Mar 5.607,31

Os dados continuam os mesmos só estão representados de outra forma. O comando UNPIVOT transformou as categorias (antes representadas em colunas) para linhas e a coluna de valor continua sendo a interseção entre uma determinada categoria e um determinado mês.

Seguindo a mesma lógica, é possível fazer com que as categorias (atualmente representadas como linhas) tornem-se colunas, efetuando a operação contrário ao UNPIVOT, através do operador PIVOT, ou seja, transformar linhas em colunas.

;WITH Res As (
SELECT * FROM
   (SELECT [Categoria],
    [Jan], [Fev], [Mar], [Abr],
    [Mai], [Jun], [Jul], [Ago],
    [Set], [Out], [Nov], [Dez]
   FROM RelatorioVendas2008) As P
UNPIVOT
   ([Valor] FOR [Mes] IN (
        [Jan], [Fev], [Mar], [Abr], [Mai], [Jun],
        [Jul], [Ago], [Set], [Out], [Nov], [Dez])
) As UP),

Meses (MesNum, MesExtenso) As (
    SELECT 01, ‘Jan’ UNION ALL
    SELECT 02, ‘Fev’ UNION ALL
    SELECT 03, ‘Mar’ UNION ALL
    SELECT 04, ‘Abr’ UNION ALL
    SELECT 05, ‘Mai’ UNION ALL
    SELECT 06, ‘Jun’ UNION ALL
    SELECT 07, ‘Jul’ UNION ALL
    SELECT 08, ‘Ago’ UNION ALL
    SELECT 09, ‘Set’ UNION ALL
    SELECT 10, ‘Out’ UNION ALL
    SELECT 11, ‘Nov’ UNION ALL
    SELECT 12, ‘Dez’)

SELECT
    [Mes], [Eletrônicos], [Vestiário], [Alimentos], [Medicamentos]
FROM
(SELECT [Categoria], [Mes], [Valor]
    FROM Res) As SC
PIVOT
(MAX([Valor])
    FOR [Categoria] IN ([Eletrônicos], [Vestiário], [Alimentos], [Medicamentos])
) As PT
    INNER JOIN Meses ON PT.Mes = Meses.MesExtenso
ORDER BY MesNum

A primeira parte do script é semelhante ao script anterior, ou seja, ele apenas "despivoteia" os dados de categoria transformando-os de colunas para linhas. A segunda parte cria uma tabela de meses com o seu número e seu valor simplificado, pois, esses dados serão necessários para ordenação posterior. A terceira parte do script "pivoteia" os dados de categoria transformando-os de linhas para colunas. O uso do operador PIVOT no Transact-SQL exige que uma função de agregação seja aplicada. Como o valor é a interseção entre uma categoria e um mês, e há apenas um valor para cada combinação categoria – mês, não fará diferença se o exemplo utilizar uma função como SUM, MIN ou AVG. A função de agregação não irá agregar de fato um único registro de valor por combinação categoria – mês, mas ela é necessária para atender à sintaxe do comando. O resultado é obtido conforme a tabela a seguir:

Mês Eletrônicos Vestiário Alimentos Medicamentos
Jan 20.396,13 70.453,60 14.097,33 4.502,56
Fev 12.430,55 58.370,59 10.054,56 3.200,18
Mar 15.467,89 56.230,33 17.240,72 5.607,31
Abr 17.536,90 60.237,20 20.540,14 6.230,89
Mai 18.370,32 63.547,25 21.042,99 6.457,63
Jun 17.960,41 68.236,97 22.340,22 6.241,55
Jul 16.435,04 62.536,88 20.760,28 6.117,74
Ago 13.430,87 57.243,90 20.870,15 5.949,52
Set 12.420,63 55.243,09 19.903,27 6.303,22
Out 14.870,12 58.210,03 20.432,84 6.177,84
Nov 19.270,53 65.497,80 22.456,00 6.098,16
Dez 24.568,09 75.889,23 25.378,19 6.559,26

Dessa forma, com pouco código foi possível "transpor" os resultados da tabela que tinha as colunas de meses e as linhas de categoria em uma outra disposição para apresentar as linhas de meses e as colunas de categoria. Alguns podem achar essa construção TSQL interessante, mas existem algumas limitações consideráveis:

  • Conhecimento Prévio dos Membros: Nas operações de UNPIVOT e de PIVOT foi necessário conhecer previamente os meses envolvidos e as categorias envolvidas. Como o exemplo foi com base em uma tabela já consolidada, o conhecimento prévio desses membros não representa dificuldades. Em boa parte dos casos, os membros não são conhecidos e ainda que o fossem, a adição de novos membros (no caso de novos meses e (ou) categorias) prejudica o desenvolvimentos de consultas desse tipo. Infelizmente até a versão atual, os operadores UNPIVOT e PIVOT não trabalham de forma dinâmica.
  • Baixo Desempenho: Para Resultsets pequenos, consultas desse tipo não fazem muita diferença (o exemplo em questão ocupa apenas um bloco de memória), mas para Resultsets grandes, essa transposição pode fazer muita diferença em termos de CPU, memória e IO. A transposição não deve ser utilizada para grandes quantidades de resultados e nem para consultas que tenha uma freqüência muito alta de execução.

Essas limitações só evidenciam uma idéia que defendo e repito muitas vezes nos clientes, fóruns, comunidades e salas de aula. A SQL é uma linguagem para recuperação e manipulação de dados e não para formatação e apresentação de dados. Embora a SQL tenha algumas funções para facilitar a apresentação e formatação, seu principal propósito está longe de ser a "transposição de linhas e colunas". Se consultas SQL tornam-se complexas para que um determinado resultado seja apresentado, possivelmente a SQL não é a solução adequada. Para tais situações, é sempre bom avaliar outras alternativas como o Reporting Services, o OWC, o Excel, a programação da aplicação e se as condições orçamentárias permitirem uma boa ferramenta OLAP.

[ ]s,

Gustavo

6 Respostas para “Pivoteando, Despivoteando, Transpondo, Invertendo Colunas e Linhas no SQL Server

  1. Gustavo, e se por acaso eu não estiver usando uma função? Como por exemplo uma listagem de vendedores e seus respetivos clientes como segue exemplo abaixo:(A) (B) (C) (D) 12301 12301 32401 1201 32401 32401 42101 1301 32501 32601 42501 1401 32601 42101 42601 135401 42201 42701 421501 42801 520101Estou diante de uma solicitação assim e estou sem saber como resolver via select.Obrigado desde já, Afrânio

  2. Olá Afranio,Não entendi muito bem sua dúvida. Você poderia detalhar melhor (em um e-mail ou em algum fórum) ?Acredito que o artigo sirva para a sua dúvida, mas preciso de mais detalhes.Abs,

  3. –Tabela de amarrações Cliente x VendedorCREATE TABLE #CLIXVEND(VENDEDOR VARCHAR(10),CLIENTE CHAR(6))–Dados da tabelaINSERT INTO #CLIXVEND VALUES (\’AFRANIO\’,\’000001\’)INSERT INTO #CLIXVEND VALUES (\’AFRANIO\’,\’000002\’)INSERT INTO #CLIXVEND VALUES (\’AFRANIO\’,\’000003\’)INSERT INTO #CLIXVEND VALUES (\’AFRANIO\’,\’000004\’)INSERT INTO #CLIXVEND VALUES (\’GUSTAVO\’,\’000001\’)INSERT INTO #CLIXVEND VALUES (\’GUSTAVO\’,\’000002\’)INSERT INTO #CLIXVEND VALUES (\’GUSTAVO\’,\’000005\’)–Resultado que gostaria que saísse:/*AFRANIO GUSTAVO000001 000001000002 000002000003 000005000004*/

  4. Olá Afranio,Para montar exatamente do mesmo jeito que você postou é bem mais complicado, mas segue uma solução um pouco adaptável.SELECT CASE WHEN AFRANIO = 1 THEN CLIENTE END As AFRANIO, CASE WHEN GUSTAVO = 1 THEN CLIENTE END As GUSTAVOFROM (SELECT Vendedor, Cliente, 1 As Val FROM #CLIXVEND) As STPIVOT(MAX(Val) FOR Vendedor IN ([AFRANIO],[GUSTAVO])) AS QAbs,

  5. Gustavo, bom dia.
    Estava lendo seu post e estou com a seguinte necessidade. Estou extraindo dados de uma base para importar numa outra. Tenho para cada parceiro de negócios vários contatos que ficam numa tabela separada. SELECT EMPCOD, EMPCONNOME, EMPCONFONE, EMPCONEMAI
    FROM EMP0103
    WHERE EMPCOD= 434
    ele me retorna em linhas o resultado… O que eu preciso é que ele retorne o resultado numa única linha… Sendo:
    EMPCOD1 EMPCONNOME1 EMPCONFONE1, EMPCONEMAI1 aí em vez de me retornar numa segunda linha, ele continuasse na mesma linha como EMPCOD2 EMPCONNOME2 EMPCONFONE2 EMPCONEMAI2 e assim por diante. Você entendeu a minha explicação? É possível fazer essa consulta? Muito obrigado… Abraço

Deixe uma resposta

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