none
Posso ter duas colunas a serem agregadas numa PIVOT? RRS feed

  • Pergunta

  • Eu desenvolvi essa query
    select PDV,SERPDV, [01],[02],[03],[04],[05],[06],[07],[08],[09],[10],
    [11],[12],[13],[14],[15],[16],[17],[18],[19],[20],
    [21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31]
    from (select PDV,SERPDV, SUBSTRING(EMISSAO,7,2)  as emissao, round(SUM(VALBRUT),2) as VALOR  from TAB1
    INNER JOIN TAB2 ON SERIE = PDV
    where NOME = 'ATIVO'
    AND SUBSTRING(EMISSAO,1,6) = '201703'
    GROUP BY PDV,SERPDV,EMISSAO) SQ
    PIVOT (SUM(VALOR) FOR emissao in ([01],[02],[03],[04],[05],[06],[07],[08],[09],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31])) AS  pt

    mas eu quero adicionar mais uma coluuna COUNT(DOC)as QTDE, cujo resultado deve ser mostrado em  [01T],[02T],[03T]... [31T]

    select PDV,SERPDV, [01],[01T],[02],[02T],[03], [03T],[04], [04T],[05],[05T],[06],[06T],[07],[07T],[08],[08T],[09],[09T],[10],[10T],[11],[11T],[12],[12T],[13],[13T],[14],[14T],[15],[15T],[16],[16T],[17],[17T],[18],[18T],[19],[19T],[20],[20T],
    [21],[21T],[22],[22T],[23],[23T],[24],[24T],[25],[25T],[26],[26T],[27],[27T],[28],[28T],[29],[29T],[30],[30T],
    [31],[31T] from 
    (select PDV,SERPDV, SUBSTRING(EMISSAO,7,2)  as emissao, SUM(VALBRUT) as VALOR, COUNT(DOC)as QTDE
       from TAB1 INNER JOIN TAB2 ON SERIE = PDV where NOME = 'ATIVO'
    AND SUBSTRING(EMISSAO,1,6) = '201703'
    GROUP BY PDV,SERPDV,EMISSAO) SQ
    PIVOT (SUM(VALOR) FOR emissao in
    ([01],[01T],[02],[02T],[03], [03T],[04], [04T],[05],[05T],[06],[06T],[07],[07T],[08],[08T],[09],[09T],[10],[10T],
    [11],[11T],[12],[12T],[13],[13T],[14],[14T],[15],[15T],[16],[16T],[17],[17T],[18],[18T],[19],[19T],[20],[20T],
    [21],[21T],[22],[22T],[23],[23T],[24],[24T],[25],[25T],[26],[26T],[27],[27T],[28],[28T],[29],[29T],[30],[30T],
    [31],[31T])) AS  pt

    Alguem poderia me ajudar?

    quarta-feira, 29 de março de 2017 16:04

Respostas

Todas as Respostas