Colocar um case ou sum dentro de um select

[asacap1000 0]

Fala galera, estou com um select e precisava dar uma ajustada nele, preciso utilizar o sum e ou case para carregar as informações em apenas uma linha, porém não estou conseguindo montar, se alguem puder me dar uma força.

Abaixo o select, o ponto principal estão em vermelho. osi.billite se refere ao tipo de serviço e o osi.preis o valor.

SELECT OSI.ID_KLIENT,BO.ID_OS,OSI.DIV_1,osi.billite,OSI.PREIS FROM BILL_OS BO, bill_os_item osi WHERE BO.ID_KLIENT = OSI.ID_KLIENT AND BO.ID_OS = OSI.ID_OS AND OSI.ID_KLIENT = '485' AND OSI.DIV_1 = 'AD13660114'

Quando realizo este select ele me traz da seguinte forma.

 

ID_KLIENT ID_OS	 DIV_1	      BILLITE	 PREIS
485	137304	AD13660114	05.02	70,00000
485	137304	AD13660114	04.01	11,11110
485	137304	AD13660114	03.03	21,60000
485	137304	AD13660114	03.01	21,60000
485	137304	AD13660114	02.01	27,65000
485	137304	AD13660114	02.01	27,65000
485	137304	AD13660114	01.01	110,59800
485	137304	AD13660114	01.01	110,59800

Porém eu preciso carregar estes valores em apenas uma linha, e com cada item do Billite com nome, e se estiver repetindo o item somar o valor.

[Motta 645]

Você pode usar um CASE com uma função de grupo como SUM ou MAX ou usar a opção do PIVOT.

 

http://www.dbanotes.com/database-development/oracle-11g-pivot-functions/

 

https://www.google.com.br/search?redir_esc=&client=tablet-android-samsung&hl=pt-BR&safe=images&oe=utf-8&q=oracle%20documentation%20pivot&source=android-browser-suggest&qsubts=1407376715683&action=devloc#hl=pt-BR&q=oracle+documentation+pivot+

 

 

http://www.oracle-base.com/articles/11g/pivot-and-unpivot-operators-11gr1.php

http://pt.stackoverflow.com/questions/22074/utilizar-o-operador-pivot-sem-agrega%C3%A7%C3%A3o

 

 

http://forum.imasters.com.br/topic/515040-listar-quantidade-de-pedidos-por-ms-sem-repetir-cliente/

[asacap1000 0]

Legal vou verificar todos esses links e ver o que consigo fazer. Só para ter idéia do tamanho do select. Hoje ele funciona assim em um dos sistemas porém o sistema não exporta para excel sendo assim estou montando para gerar a partir de uma intranet em PHP.

select x.id_klient,
       x.cnpj_cpf,
       x.name,
       x.lote,
       x.di_dde,
       x.nr_nf,
       x.date_bill,
       TO_CHAR(trunc(x.date_bill, 'month')),
               to_number(to_char(x.date_bill,'rrrrmm')) ordem,
       decode(nvl(sum(x.vlr_armaz),0),0,null,decode(nvl(x.di_dde, '0'), '0', z.valor_cif, y.valor)) valor_cif,
       (select min(io.time_in)
          from in_out io, in_out_pos iop, we
         where io.lager = iop.lager
           and io.id_in_out = iop.id_in_out
           and iop.lager = we.lager
           and iop.typ_document = we.typ_doc
           and iop.document = we.nr_doc
           and io.art_in_out = 'LI'
           and we.nr_lieferschein = x.lote) dt_entrada,
           
       (select max(io.time_out)
          from in_out io, in_out_pos iop
         where io.lager = iop.lager
           and io.id_in_out = iop.id_in_out
           and iop.typ_document in ('DI', 'DDE')
           and iop.document = x.di_dde) dt_saida,
           
       sum(x.vlr_armaz) vlr_armaz,
       sum(x.vlr_ger_risco) vlr_ger_risco,
       sum(x.vlr_movimentacao) vlr_movimentacao,
       sum(x.vlr_desunit) vlr_desunit,
       sum(x.vlr_pesagem) vlr_pesagem,
       sum(x.vlr_averbacao) vlr_averbacao,
       sum(x.vlr_fat_minimo) vlr_fat_minimo,
       sum(x.vlr_outros_serv) vlr_outros_serv,
       
       (select sum(osi.preis)
          from bill_os_item osi
         where osi.lager = x.lager
           and osi.id_klient = x.id_klient
           and osi.id_os = x.id_os
           and nvl(osi.div_1,'x') = nvl(x.lote,'x')
           and nvl(osi.div_10,'x') = nvl(x.di_dde,'x')) vlr_tot_nf
           
  from (select bo.lager,
               bo.id_os,
               k.id_klient,
               decode(length(k.suchbegriff),
                      14,
                      substr(k.suchbegriff, 1, 2) || '.' ||
                      substr(k.suchbegriff, 3, 3) || '.' ||
                      substr(k.suchbegriff, 6, 3) || '/' ||
                      substr(k.suchbegriff, 9, 4) || '-' ||
                      substr(k.suchbegriff, 13, 2),
                      substr(k.suchbegriff, 1, 3) || '.' ||
                      substr(k.suchbegriff, 4, 3) || '.' ||
                      substr(k.suchbegriff, 7, 3) || '-' ||
                      substr(k.suchbegriff, 10, 2)) cnpj_cpf,
               k.name,
               bi.div_1 lote,
               bi.div_10 di_dde,
               bo.nr_nf,
               bo.date_bill,             
               (select sum(osi.preis)
                     from bill_os_item osi
                    where osi.lager = bo.lager
                      and osi.id_klient = k.id_klient
                      and osi.id_os = bo.id_os
                      and osi.billite = bi.billite
                      and osi.div_1 = bi.div_1
                      and nvl(osi.div_10,'x') = nvl(bi.div_10,'x')
                      and osi.billite in
                          ('01.01', '01.02', '01.03', '01.04')) vlr_armaz,
               (select sum(osi.preis)
                     from bill_os_item osi
                    where osi.lager = bo.lager
                      and osi.id_klient = k.id_klient
                      and osi.id_os = bo.id_os
                      and osi.billite = bi.billite
                      and osi.div_1 = bi.div_1
                      and nvl(osi.div_10,'x') = nvl(bi.div_10,'x')
                      and osi.billite in
                          ('02.01', '02.02', '02.03', '02.04')) vlr_ger_risco,
               (select sum(osi.preis)
                     from bill_os_item osi
                    where osi.lager = bo.lager
                      and osi.id_klient = k.id_klient
                      and osi.id_os = bo.id_os
                      and osi.billite = bi.billite
                      and osi.div_1 = bi.div_1
                      and nvl(osi.div_10,'x') = nvl(bi.div_10,'x')
                      and osi.billite in ('03.01', '03.02','03.04')) vlr_movimentacao,
               (select sum(osi.preis)
                     from bill_os_item osi
                    where osi.lager = bo.lager
                      and osi.id_klient = k.id_klient
                      and osi.id_os = bo.id_os
                      and osi.billite = bi.billite
                      and osi.div_1 = bi.div_1
                      and nvl(osi.div_10,'x') = nvl(bi.div_10,'x')
                      and osi.billite in ('03.03')) vlr_desunit,
               (select sum(osi.preis)
                     from bill_os_item osi
                    where osi.lager = bo.lager
                      and osi.id_klient = k.id_klient
                      and osi.id_os = bo.id_os
                      and osi.billite = bi.billite
                      and osi.div_1 = bi.div_1
                      and nvl(osi.div_10,'x') = nvl(bi.div_10,'x')
                      and osi.billite in ('04.01', '04.02')) vlr_pesagem,
               (select sum(osi.preis)
                     from bill_os_item osi
                    where osi.lager = bo.lager
                      and osi.id_klient = k.id_klient
                      and osi.id_os = bo.id_os
                      and osi.billite = bi.billite
                      and osi.div_1 = bi.div_1
                      and nvl(osi.div_10,'x') = nvl(bi.div_10,'x')
                      and osi.billite in ('05.01', '05.02')) vlr_averbacao,
               (select sum(osi.preis)
                     from bill_os_item osi
                    where osi.lager = bo.lager
                      and osi.id_klient = k.id_klient
                      and osi.id_os = bo.id_os
                      and osi.billite = bi.billite
                      and osi.billite in ('06.01')) vlr_fat_minimo,
               (select sum(osi.preis)
                     from bill_os_item osi
                    where osi.lager = bo.lager
                      and osi.id_klient = k.id_klient
                      and osi.id_os = bo.id_os
                      and osi.billite = bi.billite
                      and osi.div_1 = bi.div_1
                      and nvl(osi.div_10,'x') = nvl(bi.div_10,'x')
                      and substr(osi.billite, 1, 2) > '07') vlr_outros_serv
      
          from bill_os bo, bill_os_item bi, klienten k
         where bo.lager = bi.lager
           and bo.id_klient = bi.id_klient
           and bo.id_os = bi.id_os
           and bo.lager = k.lager
           and bo.id_klient = k.id_klient
           and bo.status = '90'
           and bo.lager = 'PSI'
           and bo.id_klient = '862'
           and bo.lote = 'AD12755313'
          group by bo.lager,
                  bo.id_os,
                  k.id_klient,
                  decode(length(k.suchbegriff),
                         14,
                         substr(k.suchbegriff, 1, 2) || '.' ||
                         substr(k.suchbegriff, 3, 3) || '.' ||
                         substr(k.suchbegriff, 6, 3) || '/' ||
                         substr(k.suchbegriff, 9, 4) || '-' ||
                         substr(k.suchbegriff, 13, 2),
                         substr(k.suchbegriff, 1, 3) || '.' ||
                         substr(k.suchbegriff, 4, 3) || '.' ||
                         substr(k.suchbegriff, 7, 3) || '-' ||
                         substr(k.suchbegriff, 10, 2)),
                  k.name,
                  bo.nr_nf,
                  bo.date_bill,
                  bi.billite,
                  bi.div_1,
                  bi.div_10) x,
       (select MAX(d.valor_nf) valor, d.nr_di doc, d.id_klient, d.lager
          from desmembr d
         where d.typ_process = 'DINACI' group by d.nr_di, d.id_klient, d.lager
        union
        select dde.preis valor, dde.nr_dde doc, dde.id_klient, dde.lager
          from dde_reg dde) y,
       (select a.valor_cif, a.bruecke_1, a.id_klient, a.lager
         from anliefpos a) z
 where x.lager = z.lager(+)
   and x.id_klient = z.id_klient(+)
   and x.lote = z.bruecke_1(+)
   and TRIM(x.di_dde) = TRIM(y.doc(+))
    and x.id_klient = '862'
    and x.lote = 'AD12755313'
 group by x.lager,
          x.id_klient,
          x.id_os,
          x.cnpj_cpf,
          x.name,
          x.lote,
          x.di_dde,
          x.nr_nf,
          x.date_bill,
          TO_CHAR(trunc(x.date_bill, 'month')
     ),
          z.valor_cif,
          y.valor
 order by x.date_bill, x.id_os, x.di_dde, x.lote

é osso mas vamos a luta que consigo.

[Motta 645]

Eu faria deste sql uma view simplificaria a sql do PIVOT.

 

Uma outra solução é usar geradores de rel. como Crystal e outros que fazer Ref. Cruzada , tem a vantagem das colunas não ficarem "chumbadas" como com PIVOT ou CASE.

 

Até o excel faz ests Ref. Cruzada (Cross Ref).