Compartilhar via

T-SQL: PIVOT dinâmico em várias colunas

  • Artigo

O problema da transposição linhas em colunas é um dos problemas mais comuns discutidos no fórum Transact-SQL MSDN. Muitas vezes, o problema da criação de um pivô dinâmico vem à tona. Uma coisa que muitas pessoas que fazem essa pergunta esquecem é que tal transposição é muito mais fácil de executar no lado do cliente do que no lado do servidor onde precisamos recorrer à consulta dinâmica. 

No entanto, se queremos fazer um PIVOT dinâmico, é importante entender que escrever uma consulta dinâmica é só um pouco mais difícil do que escrever consulta estática. Na verdade, quando eu sou apresentado com o problema do PIVOT dinâmico, eu primeiro descubro como consulta estática deve ser parecida. Então fazer essa consulta dinâmica se torna uma tarefa trivial. 

Eu escrevi sobre "PIVOT dinâmico em várias colunas" antes neste post do blog:  PIVOT dinâmico em várias colunas. 

Eu não quero voltar a dizer o que eu já disse no blog neste artigo por isso vou mostrar um outro exemplo de um recente thread sobre o tema. Neste thread eu apresentei a seguinte solução para o problema do PIVOT dinâmico para um número desconhecido de colunas.

USE tempdb
GO
 
CREATE TABLE tblTest (
    Id INT
    ,Col_1 INT
    )
 
INSERT INTO tblTest
VALUES (
    1
    ,12345
    )
    ,(
    1
    ,23456
    )
    ,(
    1
    ,45678
    )
    ,(
    2
    ,57823
    )
    ,(
    2
    ,11111
    )
    ,(
    2
    ,34304
    )
    ,(
    2
    ,12344
    )
 
DECLARE @MaxCount INT;
 
SELECT @MaxCount = max(cnt)
FROM (
    SELECT Id
        ,count(Col_1) AS cnt
    FROM tblTest
    GROUP BY Id
    ) X;
 
DECLARE @SQL NVARCHAR(max)
    ,@i INT;
 
SET @i = 0;
SET @SQL = '';
 
WHILE @i < @MaxCount
BEGIN
    SET @i = @i + 1;
    SET @SQL = @Sql + ',
    MAX(CASE WHEN RowNo = ' + cast(@i AS NVARCHAR(10)) + ' THEN  Col_1 END) AS Col' + cast(@i AS NVARCHAR(10));
END
 
SET @SQL = N';WITH CTE AS (
   SELECT ID, Col_1, row_number() OVER (PARTITION BY ID ORDER BY Col_1) AS rowno
   FROM   tblTest
)
SELECT ID ' + @SQL + N'
FROM   CTE
GROUP  BY ID';
 
PRINT @SQL;
 
EXECUTE (@SQL);

Nesta solução o primeiro passo foi descobrir a solução estática usando ROW_NUMBER() com abordagem de partição. Este "CASE" é baseado em PIVOT embora poderíamos ter usado a verdadeira sintaxe PIVOT aqui em vez disso. O "CASE" baseado no PIVOT é mais fácil de usar, se precisamos transpor múltiplas colunas. Uma vez que conhecemos o PIVOT estático, somos capazes de transformá-lo facilmente em dinâmico usando o loop WHILE.

Apenas para completar, eu também mostro abaixo o mesmo problema resolvido usando a sintaxe PIVOT:


DECLARE @MaxCount INT;
 
SELECT @MaxCount = max(cnt)
FROM (
    SELECT Id
        ,count(Col_1) AS cnt
    FROM tblTest
    GROUP BY Id
    ) X;
 
DECLARE @SQL NVARCHAR(max)
    ,@i INT;
 
SET @i = 0;
 
WHILE @i < @MaxCount 
BEGIN
    SET @i = @i + 1;
    SET @SQL = COALESCE(@Sql + ', ', '') + 'Col' + cast(@i AS NVARCHAR(10));
END
 
SET @SQL = N';WITH CTE AS (
   SELECT ID, Col_1, ''Col'' + CAST(row_number() OVER (PARTITION BY ID ORDER BY Col_1) AS Varchar(10)) AS RowNo
   FROM   tblTest
)
SELECT *
FROM   CTE
PIVOT (MAX(Col_1) FOR RowNo IN (' + @SQL + N')) pvt';
 
PRINT @SQL;
 
EXECUTE (@SQL);

Como você pode ver, o código é muito semelhante à primeira solução, mas usando a sintaxe PIVOT em vez de CASE baseado em PIVOT.

Espero que para adicionar mais exemplos para este artigo apareçam novas oportunidades.

Havia outra questão recente sobre PIVOT dinâmico, onde a solução deste artigo foi certeira.

Recursos Adicionais

Veja Também

Outros Idiomas