none
transformar resultados de linha para coluna RRS feed

  • Pergunta

  • Seri que existe a instrução PIVOT do sql que aliás já usei algumas vezes mas não vejo uma forma de usa-la neste caso.

    Tenho uma consulta que retorna uma quantidade de clientes agrupada por estado (UF). A instrução usa um COUNT(codCliente) e UF ficando assim

    Quantidade | UF
    1345          | SP
    323            | RJ

    e assim por diante para todos os Estados

    Agora preciso fazer com que esses nomes de Estados se tornem nomes das colunas ficando os registros na linha com a quantidade respectiva de cada coluna. Isto é:

    SP     |  RJ
    1345  |  323  

    Se o Estado não tiver nenhum registro tanto faz retornar um zero na sua coluna quanto não retornar nada. Isso não é importante.


    segunda-feira, 14 de dezembro de 2009 21:56

Respostas

  • Boa noite spitzmann, segue exemplo de pivot, conforme os dados informados.

    -- Criando tab temporária (cte) com os dados
    ;with cte (codCliente, UF)
    as
    (
        SELECT 1    , 'SP' UNION 
        SELECT 1    , 'RJ' UNION 
        SELECT NULL , 'MG'
    	
        UNION ALL
    	
        SELECT codCliente+1, UF 
        FROM   CTE 
        WHERE 
           (codCliente < 1345 AND UF = 'SP') 
        OR (codCliente < 323  AND UF = 'RJ')
    )
    
    -- Executando select com PIVOT
    SELECT [SP],[RJ],[MG]
    FROM
    (
        SELECT  ISNULL(COUNT(codCliente),0) as QTD, UF 
        FROM    cte 
        GROUP BY UF 
    ) as X
    PIVOT
    ( 
      MAX(qtd) 
      FOR UF IN ([SP],[RJ],[MG])  
    ) AS pvt
    OPTION (maxrecursion 0) --> utilizado por causa do CTE
    
    
    

    Se a minha ajuda lhe for útil não esqueça de classificar. Att. Leonardo Marcelino
    • Sugerido como Resposta Leonardo Marcelino segunda-feira, 14 de dezembro de 2009 23:43
    • Marcado como Resposta spitzmann terça-feira, 15 de dezembro de 2009 16:46
    segunda-feira, 14 de dezembro de 2009 23:43
  • sptizman,

    Acredito que o Pivot poderia se encaixar, bem como, um CTE.

    Veja este exemplo:

    CREATE

     

    TABLE CLIENTES

     

     

    (CODIGO INT IDENTITY(1,1),

    NOME

    VARCHAR(20),

    UF

    CHAR(2) DEFAULT 'SP')

     

     

     

    INSERT INTO CLIENTES VALUES ('PEDRO','SP')

     

    INSERT INTO CLIENTES VALUES ('JUNIOR','SP')

     

    INSERT INTO CLIENTES VALUES ('ANTONIO','SP')

     

     

    INSERT INTO CLIENTES VALUES ('SILVA','RJ')

     

    INSERT INTO CLIENTES VALUES ('SILVIO','RJ')

     

     

    INSERT INTO CLIENTES VALUES ('ANDRE','AC')

     

     

    INSERT INTO CLIENTES VALUES ('FERNANDA','MG')

     

    INSERT INTO CLIENTES VALUES ('FERNANDO','MG')

     

     

    SELECT * FROM CLIENTES

     

     

    ;WITH Estados (SP, RJ, MG, AC)

     

    AS

     

     

    (

     

    Select Distinct

     

     

    (SELECT COUNT(CODIGO) FROM CLIENTES WHERE UF='SP') AS SP,

     

     

    (SELECT COUNT(CODIGO) FROM CLIENTES WHERE UF='MG') AS MG,

     

     

    (SELECT COUNT(CODIGO) FROM CLIENTES WHERE UF='RJ') AS RJ,

     

     

    (SELECT COUNT(CODIGO) FROM CLIENTES WHERE UF='AC') AS AC

     

    From Clientes

     

    )

     

    SELECT

     

    * FROM Estados


    Pedro Antonio Galvão Junior - MVP - Windows Server System - SQL Server/Coordenador de Projetos/DBA
    • Marcado como Resposta spitzmann terça-feira, 15 de dezembro de 2009 16:46
    segunda-feira, 14 de dezembro de 2009 23:44
  • Aproveitando os dados do Junior Galvão, segue tb outro exemplo q simula a função pivot.
    antes do sql 2005 esta era uma das formas de simula-lo.

    DECLARE @CLIENTES TABLE 
    (CODIGO INT IDENTITY(1,1),
     NOME VARCHAR(20), 
     UF CHAR(2) DEFAULT 'SP'
     )
    
     
    INSERT INTO @CLIENTES VALUES ('PEDRO','SP')
    INSERT INTO @CLIENTES VALUES ('JUNIOR','SP')
    INSERT INTO @CLIENTES VALUES ('ANTONIO','SP')
    INSERT INTO @CLIENTES VALUES ('SILVA','RJ')
    INSERT INTO @CLIENTES VALUES ('SILVIO','RJ') 
    INSERT INTO @CLIENTES VALUES ('ANDRE','AC')
    INSERT INTO @CLIENTES VALUES ('FERNANDA','MG')
    INSERT INTO @CLIENTES VALUES ('FERNANDO','MG')
    
    SELECT COUNT(NOME) AS QTD, UF
    FROM @CLIENTES 
    GROUP BY UF
    
    SELECT
        SP = COUNT(CASE UF WHEN 'SP' THEN NOME END), 
        RJ = COUNT(CASE UF WHEN 'RJ' THEN NOME END),  
        MG = COUNT(CASE UF WHEN 'MG' THEN NOME END),  
        AC = COUNT(CASE UF WHEN 'AC' THEN NOME END)
    FROM @CLIENTES 


    Se a minha ajuda lhe for útil não esqueça de classificar. Att. Leonardo Marcelino
    • Marcado como Resposta spitzmann terça-feira, 15 de dezembro de 2009 16:46
    terça-feira, 15 de dezembro de 2009 00:13

Todas as Respostas

  • Boa noite spitzmann, segue exemplo de pivot, conforme os dados informados.

    -- Criando tab temporária (cte) com os dados
    ;with cte (codCliente, UF)
    as
    (
        SELECT 1    , 'SP' UNION 
        SELECT 1    , 'RJ' UNION 
        SELECT NULL , 'MG'
    	
        UNION ALL
    	
        SELECT codCliente+1, UF 
        FROM   CTE 
        WHERE 
           (codCliente < 1345 AND UF = 'SP') 
        OR (codCliente < 323  AND UF = 'RJ')
    )
    
    -- Executando select com PIVOT
    SELECT [SP],[RJ],[MG]
    FROM
    (
        SELECT  ISNULL(COUNT(codCliente),0) as QTD, UF 
        FROM    cte 
        GROUP BY UF 
    ) as X
    PIVOT
    ( 
      MAX(qtd) 
      FOR UF IN ([SP],[RJ],[MG])  
    ) AS pvt
    OPTION (maxrecursion 0) --> utilizado por causa do CTE
    
    
    

    Se a minha ajuda lhe for útil não esqueça de classificar. Att. Leonardo Marcelino
    • Sugerido como Resposta Leonardo Marcelino segunda-feira, 14 de dezembro de 2009 23:43
    • Marcado como Resposta spitzmann terça-feira, 15 de dezembro de 2009 16:46
    segunda-feira, 14 de dezembro de 2009 23:43
  • sptizman,

    Acredito que o Pivot poderia se encaixar, bem como, um CTE.

    Veja este exemplo:

    CREATE

     

    TABLE CLIENTES

     

     

    (CODIGO INT IDENTITY(1,1),

    NOME

    VARCHAR(20),

    UF

    CHAR(2) DEFAULT 'SP')

     

     

     

    INSERT INTO CLIENTES VALUES ('PEDRO','SP')

     

    INSERT INTO CLIENTES VALUES ('JUNIOR','SP')

     

    INSERT INTO CLIENTES VALUES ('ANTONIO','SP')

     

     

    INSERT INTO CLIENTES VALUES ('SILVA','RJ')

     

    INSERT INTO CLIENTES VALUES ('SILVIO','RJ')

     

     

    INSERT INTO CLIENTES VALUES ('ANDRE','AC')

     

     

    INSERT INTO CLIENTES VALUES ('FERNANDA','MG')

     

    INSERT INTO CLIENTES VALUES ('FERNANDO','MG')

     

     

    SELECT * FROM CLIENTES

     

     

    ;WITH Estados (SP, RJ, MG, AC)

     

    AS

     

     

    (

     

    Select Distinct

     

     

    (SELECT COUNT(CODIGO) FROM CLIENTES WHERE UF='SP') AS SP,

     

     

    (SELECT COUNT(CODIGO) FROM CLIENTES WHERE UF='MG') AS MG,

     

     

    (SELECT COUNT(CODIGO) FROM CLIENTES WHERE UF='RJ') AS RJ,

     

     

    (SELECT COUNT(CODIGO) FROM CLIENTES WHERE UF='AC') AS AC

     

    From Clientes

     

    )

     

    SELECT

     

    * FROM Estados


    Pedro Antonio Galvão Junior - MVP - Windows Server System - SQL Server/Coordenador de Projetos/DBA
    • Marcado como Resposta spitzmann terça-feira, 15 de dezembro de 2009 16:46
    segunda-feira, 14 de dezembro de 2009 23:44
  • Aproveitando os dados do Junior Galvão, segue tb outro exemplo q simula a função pivot.
    antes do sql 2005 esta era uma das formas de simula-lo.

    DECLARE @CLIENTES TABLE 
    (CODIGO INT IDENTITY(1,1),
     NOME VARCHAR(20), 
     UF CHAR(2) DEFAULT 'SP'
     )
    
     
    INSERT INTO @CLIENTES VALUES ('PEDRO','SP')
    INSERT INTO @CLIENTES VALUES ('JUNIOR','SP')
    INSERT INTO @CLIENTES VALUES ('ANTONIO','SP')
    INSERT INTO @CLIENTES VALUES ('SILVA','RJ')
    INSERT INTO @CLIENTES VALUES ('SILVIO','RJ') 
    INSERT INTO @CLIENTES VALUES ('ANDRE','AC')
    INSERT INTO @CLIENTES VALUES ('FERNANDA','MG')
    INSERT INTO @CLIENTES VALUES ('FERNANDO','MG')
    
    SELECT COUNT(NOME) AS QTD, UF
    FROM @CLIENTES 
    GROUP BY UF
    
    SELECT
        SP = COUNT(CASE UF WHEN 'SP' THEN NOME END), 
        RJ = COUNT(CASE UF WHEN 'RJ' THEN NOME END),  
        MG = COUNT(CASE UF WHEN 'MG' THEN NOME END),  
        AC = COUNT(CASE UF WHEN 'AC' THEN NOME END)
    FROM @CLIENTES 


    Se a minha ajuda lhe for útil não esqueça de classificar. Att. Leonardo Marcelino
    • Marcado como Resposta spitzmann terça-feira, 15 de dezembro de 2009 16:46
    terça-feira, 15 de dezembro de 2009 00:13
  • Leonardo,

    Com certeza o Case é um recurso fantástico, eu utilizo muito.
    Pedro Antonio Galvão Junior - MVP - Windows Server System - SQL Server/Coordenador de Projetos/DBA
    terça-feira, 15 de dezembro de 2009 00:21
  • Bom Dia,

    Em complemento às soluções postadas, eu sugiro efetuar uma pesquisa prévia, pois, certamente você encontrará muitas soluções já que é uma dúvida bastante recorrente. Se desejar tornar as coisas um pouco mais dinâmicas, segue um exemplo:

    CREATE TABLE CLIENTES (
     CODIGO INT IDENTITY(1,1),
     NOME VARCHAR(20), 
     UF CHAR(2) DEFAULT 'SP')
    INSERT INTO CLIENTES VALUES ('PEDRO','SP')
    INSERT INTO CLIENTES VALUES ('JUNIOR','SP')
    INSERT INTO CLIENTES VALUES ('ANTONIO','SP')
    INSERT INTO CLIENTES VALUES ('SILVA','RJ')
    INSERT INTO CLIENTES VALUES ('SILVIO','RJ') 
    INSERT INTO CLIENTES VALUES ('ANDRE','AC')
    INSERT INTO CLIENTES VALUES ('FERNANDA','MG')
    INSERT INTO CLIENTES VALUES ('FERNANDO','MG')
    DECLARE @QTD INT, @i INT, @cmdSQL VARCHAR(8000), @Sufixo VARCHAR(100), @UF CHAR(2)
    SET @QTD = (SELECT COUNT(DISTINCT UF) FROM CLIENTES)
    SET @i = 1
    SET @cmdSQL = 'SELECT COUNT(*) As Total '
    SET @Sufixo = CHAR(10) + '    ,[?] = COUNT(CASE UF WHEN ''?'' THEN CODIGO END)'
    SET @UF = ''
    WHILE @i <= @QTD
    BEGIN
     SET @UF = (SELECT TOP (1) UF FROM CLIENTES WHERE UF > @UF ORDER BY UF) 
     SET @cmdSQL = @cmdSQL + REPLACE(@Sufixo,'?',@UF)
     SET @i = @i + 1
    END
    SET @cmdSQL = @cmdSQL + CHAR(10) + 'FROM CLIENTES'
    EXEC(@cmdSQL)
    PRINT @cmdSQL


    Maiores detalhes em:

    Transformando Linhas em Colunas com o SQL Server 2005
    http://gustavomaiaaguiar.spaces.live.com/blog/cns!F4F5C630410B9865!178.entry

    Pivoteando, Despivoteando, Transpondo, Invertendo Colunas e Linhas no SQL Server
    http://cid-f4f5c630410b9865.spaces.live.com/blog/cns!F4F5C630410B9865!629.entry

    [ ]s,

    Gustavo Maia Aguiar
    http://gustavomaiaaguiar.spaces.live.com

    SQL Server Saturday Night
    http://gustavomaiaaguiar.spaces.live.com/blog/cns!F4F5C630410B9865!878.entry


    Classifique as respostas. O seu feedback é imprescindível
    terça-feira, 15 de dezembro de 2009 11:59
  • Maia,

    É verdade, este tipo de assunto já foi bem discutido.

    Acho muito legal o seu exemplo.
    Pedro Antonio Galvão Junior - MVP - Windows Server System - SQL Server/Coordenador de Projetos/DBA
    terça-feira, 15 de dezembro de 2009 13:23
  •  :)


    Classifique as respostas. O seu feedback é imprescindível
    terça-feira, 15 de dezembro de 2009 13:42