Rép. : [sqr-users] How to have dynamic column headers based on a selectfrom a table

[LEBLANC Philippe FTC <p.leblanc@francetelecom.com>]

Here is an example with cumulatives months:
(you have to use a sub select)

select Site
||';'||Designation
||';'||sum(CumulJanvier)
||';'||sum(CumulFevrier)
||';'||sum(CumulMars)
||';'||sum(CumulAvril)
||';'||sum(CumulMai)
||';'||sum(CumulJuin)
||';'||sum(CumulJuillet)
||';'||sum(CumulAout)
||';'||sum(CumulSeptembre)
||';'||sum(CumulOctobre)
||';'||sum(CumulNovembre)
||';'||sum(CumulDecembre) as Transferts
from (
select distinct
       cli_sit                                                                 
as Site,
       replace(ltrim(rtrim(stc_nom_sit)),' ','_')                              
as Designation,
       count(distinct decode(to_char(hide_dat_demen,'mm'),'01',prlg_nd,null) ) 
as CumulJanvier,
       count(distinct decode(to_char(hide_dat_demen,'mm'),'02',prlg_nd,null) ) 
as CumulFevrier,
       count(distinct decode(to_char(hide_dat_demen,'mm'),'03',prlg_nd,null) ) 
as CumulMars,
       count(distinct decode(to_char(hide_dat_demen,'mm'),'04',prlg_nd,null) ) 
as CumulAvril,
       count(distinct decode(to_char(hide_dat_demen,'mm'),'05',prlg_nd,null) ) 
as CumulMai,
       count(distinct decode(to_char(hide_dat_demen,'mm'),'06',prlg_nd,null) ) 
as CumulJuin,
       count(distinct decode(to_char(hide_dat_demen,'mm'),'07',prlg_nd,null) ) 
as CumulJuillet,
       count(distinct decode(to_char(hide_dat_demen,'mm'),'08',prlg_nd,null) ) 
as CumulAout,
       count(distinct decode(to_char(hide_dat_demen,'mm'),'09',prlg_nd,null) ) 
as CumulSeptembre,
       count(distinct decode(to_char(hide_dat_demen,'mm'),'10',prlg_nd,null) ) 
as CumulOctobre,
       count(distinct decode(to_char(hide_dat_demen,'mm'),'11',prlg_nd,null) ) 
as CumulNovembre,
       count(distinct decode(to_char(hide_dat_demen,'mm'),'12',prlg_nd,null) ) 
as CumulDecembre
  from rue_commune,
       adresse_site, 
       noeud cd,
       noeud prise,
       prise_logement_contrat,
       prise_logement,
       hist_demenagement,
       client,
       site_tv_cable,
       contrat
 where ruco_comu                       = adr_comu
   and ruco_riv                        = adr_riv
   and adr_adr_sit                     = prise.nd_adr_sit
   and adr_sit                         = prise.nd_sit
   and cd.nd_nd                        = prise.nd_cd_nd
   and cd.nd_sit                       = prise.nd_sit
   and prise.nd_nd                     = prlg_nd
   and prise.nd_sit                    = prlg_sit
   and prlc_dat_crea                   = ( select max(plc.prlc_dat_crea)
                                             from prise_logement_contrat plc
                                            where plc.prlc_dat_inva is not null
                                              and plc.prlc_pris_nd   = prlg_nd
                                              and plc.prlc_sit       = prlg_sit
                                            group by plc.prlc_sit, 
plc.prlc_pris_nd )
   and prlc_dat_inva                  is not null
   and prlc_pris_nd                    = prlg_nd
   and prlc_sit                        = prlg_sit
   and prlg_nd                         = hide_nd_ori
   and prlg_sit                        = hide_sit
   and hide_dat_demen                  = ( select max(hd.hide_dat_demen)
                                             from hist_demenagement hd
                                            where trunc(hd.hide_dat_demen) 
between trunc(to_date('01/01/2003','dd/mm/yyyy'))
                                                                               
and trunc(to_date('31/12/2003','dd/mm/yyyy'))
                                              and hd.hide_ctrt = cont_ctrt
                                              and hd.hide_ami  = cont_ami 
                                              and hd.hide_sit  = cont_sit 
                                            group by hd.hide_sit, hd.hide_ctrt, 
hd.hide_ami )
   and hide_ctrt                       = cont_ctrt
   and hide_ami                        = cont_ami
   and hide_sit                        = cont_sit
   and cli_clt                         = cont_clt_ctrt
   and cli_sit                         = cont_sit
   and stc_sit                         = hide_sit
   and (          cont_dat_efft_resl  is null
         or trunc(cont_dat_efft_resl) >  
trunc(to_date('31/12/2003','dd/mm/yyyy')) )
   and cont_sit                       in 
('801','802','952','AUB','MTG','503','603','201','240','301','401','502','451','CES','452','453','601','602','901','953','954')
 group by cli_sit, stc_nom_sit, to_char(hide_dat_demen,'mm')
 )
 group by Site, Designation
 order by 1;

Je reste à votre disposition pour toute demande d'information complémentaire.

Cordialement, Philippe LEBLANC
France Télécom Câble
Direction Informatique
Cellule Finances & Statistiques
Assistance centralisée: 01.55.58.36.00

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users