none
Dúvida com pivot ! RRS feed

  • Pergunta

  • Opa e aee pessoal,


    beleza? gente estou com uma dúvida com relação ao pivot (na verdade eu não consigo montar de jeito nenhum) o seguinte caso:nomeu sql eu tenho sum, sub-select e algumas condições e eu preciso transformar a saída em colunas mas não estou conseguindo usar o pivot... na saida eu tenho assim:

    familia       pendentes    media ultimo 3 meses      dias

    celular       903                         1558                      17

    desk          302                         287                        30

    monitor     321                         2758                      14

    tv               775                         1738                      17

    Só que eu preciso que as linhas celular, desk, monitor e tv virem colunas... alguem tem alguma ideia de como me ajudar?

    Obrigado pessoal!

    sql que eu fiz:

    select
     CF7.DESCRICAO AS FAMILIA,
     CAST(sum(COL1.MTR1) AS INT) AS PENDENTES,
     CAST(COL2.MEDIA AS INT) [MEDIA ULTIMOS 3 MESES],
     CAST((sum(cast(COL1.MTR1 as float))/
       CASE COL2.MEDIA    
       WHEN 0 THEN 1 ELSE cast(COL2.MEDIA as float) END)*30 AS INT) AS DIAS
    from
     CAD_FATO10ST c10 INNER JOIN
     FATO10ST COL1   on c10.FATO10 = COL1.FATO10 inner join
     CAD_FATO7ST CF7 ON COL1.FATO7 = CF7.FATO7 INNER JOIN
     (Select
      f10.FATO7,
      cast(sum(f10.MTR1)/3 as int) AS MEDIA
     from
      FATO10ST f10
      inner join CAD_FATO10ST c10 on c10.FATO10 = f10.FATO10
     where
      FATO4 = 3
      and TIPO <> 'A'
      and FATO5 <> 7
      and C10.SETOR_ATUAL <> 'R'   
      and ((MONTH(DATA_FECHAMENTO) = MONTH(DATEADD(month,-1, SYSDATETIME())) and year(DATA_FECHAMENTO) = year(DATEADD(month,-1, SYSDATETIME())))
      or (MONTH(DATA_FECHAMENTO) = MONTH(DATEADD(month,-2, SYSDATETIME())) and year(DATA_FECHAMENTO) = year(DATEADD(month,-2, SYSDATETIME())))
      or (MONTH(DATA_FECHAMENTO) = MONTH(DATEADD(month,-3, SYSDATETIME())) and year(DATA_FECHAMENTO) = year(DATEADD(month,-3, SYSDATETIME()))))
     group by
      f10.FATO7) COL2 ON COL1.FATO7 = COL2.FATO7
     
    where
     COL1.TIPO = 'A'
     and COL1.FATO5 <> 7
     and COL1.FATO4 = 3
     and C10.SETOR_ATUAL <> 'R'   
     AND COL1.FATO7 IN (1,3,5,7)
     
    group by
     CF7.DESCRICAO,
     COL2.MEDIA
    order by  CF7.DESCRICAO

    sexta-feira, 30 de novembro de 2012 11:29

Todas as Respostas

  • Akir4,

    Veja se estes exemplos ajudam:

    Create table #Exemplo (codigo int, nome varchar(10), idUsuario int)
    
     
    
    insert into #Exemplo (codigo, nome, idUsuario) Values (1,'jose', 12)
    
    insert into #Exemplo (codigo, nome, idUsuario) Values (2,'mario', 7)
    
    insert into #Exemplo (codigo, nome, idUsuario) Values (1,'jose', 14)
    
    insert into #Exemplo (codigo, nome, idUsuario) Values (2,'mario', 12)
    
    insert into #Exemplo (codigo, nome, idUsuario) Values (3,'celso', 7)
    
    insert into #Exemplo (codigo, nome, idUsuario) Values (4,'andre', 14)
    
    
    
    Select [jose],[mario],[celso],[andre] from #exemplo 
    
    Pivot (count(codigo) for nome in ([jose],[mario],[celso],[andre])) p
    
    -- CTE com Pivot --
    with consulta as (select codigo, nome from #Exemplo) 
    
    Select [jose],[mario],[celso],[andre] from Consulta 
    
    Pivot (count(codigo) for nome in ([jose],[mario],[celso],[andre])) p
    
    
    -- Trabalhando com funções de agregação no Pivot
    CREATE TABLE Sales (EmpId INT, Yr INT, Sales MONEY)
    
    INSERT Sales VALUES(1, 2005, 12000)
    
    INSERT Sales VALUES(1, 2006, 18000)
    
    INSERT Sales VALUES(1, 2007, 25000)
    
    INSERT Sales VALUES(2, 2005, 15000)
    
    INSERT Sales VALUES(2, 2006, 6000)
    
    INSERT Sales VALUES(3, 2006, 20000)
    
    INSERT Sales VALUES(3, 2007, 24000)
    
     
    
    SELECT [2005], [2006], [2007]
    
    FROM (SELECT Yr, Sales FROM Sales) AS s
    
    PIVOT (SUM(Sales) FOR Yr IN ([2005], [2006], [2007])) AS p
    
    
    SELECT [2005], [2006], [2007], [2005] + [2006] + [2007] As Total
    
    FROM (SELECT Yr, Sales FROM Sales) AS s
    
    PIVOT (SUM(Sales) FOR Yr IN ([2005], [2006], [2007])) AS p

    declare @pessoas as table (id int, nome varchar(30))
    
    declare @telefones as table (idpessoa int, tel varchar(9))
    
    insert into @pessoas values(1,'Marcelo')
    insert into @pessoas values(2,'Camila')
    insert into @pessoas values(3,'Tricolor')
    
    insert into @telefones values(1,'123-1234')
    insert into @telefones values(1,'222-2222')
    insert into @telefones values(1,'333-3333')
    insert into @telefones values(2,'123-1234')
    insert into @telefones values(2,'4444-4234')
    insert into @telefones values(2,'1406')
    insert into @telefones values(3,'125487-12')
    
    ;with cte
    as
    (
    select id,nome,tel, row_number() over (partition by id,nome order by id,nome ) as contador from @pessoas a inner join @telefones b ON a.id = b.idpessoa
    )
    select * from 
    (select id,nome,tel,contador from cte ) d
        pivot (max(tel) for contador in ([1],[2],[3])) as pvt
    


    Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário | MSIT.com]

    sexta-feira, 30 de novembro de 2012 17:10
  • Ajudar até ajudou, mas ainda não consegui colocar no contexto. Ele está apresentando uns erros. O Sql que eu fiz foi:

    use DW_DATA
    go

    select
     CF7.DESCRICAO AS FAMILIA,
     CAST(sum(COL1.MTR1) AS INT) AS PENDENTES,
     CAST(COL2.MEDIA AS INT) [MEDIA ULTIMOS 3 MESES],
     CAST((sum(cast(COL1.MTR1 as float))/CASE COL2.MEDIA WHEN 0 THEN 1 ELSE cast(COL2.MEDIA as float) END)*30 AS INT) AS DIAS
    from
     CAD_FATO10ST c10 INNER JOIN
     FATO10ST COL1   on c10.FATO10 = COL1.FATO10 inner join
     CAD_FATO7ST CF7 ON COL1.FATO7 = CF7.FATO7 INNER JOIN
     (Select
      f10.FATO7,
      cast(sum(f10.MTR1)/3 as int) AS MEDIA
     from
      FATO10ST f10
      inner join CAD_FATO10ST c10 on c10.FATO10 = f10.FATO10
     where
      FATO4 = 3
      and TIPO <> 'A'
      and FATO5 <> 7
     and C10.SETOR_ATUAL <> 'R'   
    and ((MONTH(DATA_FECHAMENTO) = MONTH(DATEADD(month,-1, SYSDATETIME())) and year(DATA_FECHAMENTO) = year(DATEADD(month,-1, SYSDATETIME())))
      or (MONTH(DATA_FECHAMENTO) = MONTH(DATEADD(month,-2, SYSDATETIME())) and year(DATA_FECHAMENTO) = year(DATEADD(month,-2, SYSDATETIME())))
      or (MONTH(DATA_FECHAMENTO) = MONTH(DATEADD(month,-3, SYSDATETIME())) and year(DATA_FECHAMENTO) = year(DATEADD(month,-3, SYSDATETIME()))))
     group by
      f10.FATO7) COL2 ON COL1.FATO7 = COL2.FATO7 

      pivot (count(CF7.DESCRICAO) for CF7.DESCRICAO in ([CELULAR], [DESK], [NOTE], [TV])) AS p --linha que adicionei
     
    where
     COL1.TIPO = 'A'
     and COL1.FATO5 <> 7
     and COL1.FATO4 = 3
     and C10.SETOR_ATUAL <> 'R'   
     AND COL1.FATO7 IN (1,3,5,7)
    group by
     CF7.DESCRICAO,
     COL2.MEDIA
    order by  CF7.DESCRICAO ;

    e tem dados os erros:

    Msg 8156, Level 16, State 1, Line 28
    The column 'FATO10' was specified multiple times for 'p'.
    Msg 4104, Level 16, State 1, Line 31
    The multi-part identifier "COL1.TIPO" could not be bound.
    Msg 4104, Level 16, State 1, Line 32
    The multi-part identifier "COL1.FATO5" could not be bound.
    Msg 4104, Level 16, State 1, Line 33
    The multi-part identifier "COL1.FATO4" could not be bound.
    Msg 4104, Level 16, State 1, Line 34
    The multi-part identifier "C10.SETOR_ATUAL" could not be bound.
    Msg 4104, Level 16, State 1, Line 35
    The multi-part identifier "COL1.FATO7" could not be bound.
    Msg 4104, Level 16, State 1, Line 35
    The multi-part identifier "COL1.FATO7" could not be bound.
    Msg 4104, Level 16, State 1, Line 35
    The multi-part identifier "COL1.FATO7" could not be bound.
    Msg 4104, Level 16, State 1, Line 35
    The multi-part identifier "COL1.FATO7" could not be bound.
    Msg 4104, Level 16, State 1, Line 37
    The multi-part identifier "CF7.DESCRICAO" could not be bound.
    Msg 4104, Level 16, State 1, Line 38
    The multi-part identifier "COL2.MEDIA" could not be bound.
    Msg 4104, Level 16, State 1, Line 3
    The multi-part identifier "CF7.DESCRICAO" could not be bound.
    Msg 4104, Level 16, State 1, Line 4
    The multi-part identifier "COL1.MTR1" could not be bound.
    Msg 4104, Level 16, State 1, Line 5
    The multi-part identifier "COL2.MEDIA" could not be bound.
    Msg 4104, Level 16, State 1, Line 6
    The multi-part identifier "COL1.MTR1" could not be bound.
    Msg 4104, Level 16, State 1, Line 6
    The multi-part identifier "COL2.MEDIA" could not be bound.
    Msg 4104, Level 16, State 1, Line 6
    The multi-part identifier "COL2.MEDIA" could not be bound.
    Msg 4104, Level 16, State 1, Line 39
    The multi-part identifier "CF7.DESCRICAO" could not be bound.

    nao sei como resolver ainda...

    sexta-feira, 30 de novembro de 2012 17:20