1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
| with cte_all_nums (matricule, categorie, numtel, rn) as
(
select inf.matricule
, case when substr(ctc.numtel, 1, 2) in ('06', '07') then 'MOBILE' else 'FIXE' end
, ctc.numtel
, row_number() over(partition by case when substr(ctc.numtel, 1, 2) in ('06', '07') then 'MOBILE' else 'FIXE' end order by ctc.numtel asc)
from INFORMATION inf
left join CONTACT ctc on ctc.id_information = inf.id_information
and ctc.numtel is not null
where inf.matricule is not null
)
select *
from cte_all_nums
pivot (max(numtel) for (categorie, rn) in ( ('FIXE' , 1) as "FIXE N°1"
, ('FIXE' , 2) as "FIXE N°2"
, ('MOBILE', 1) as "MOBILE N°1"
, ('MOBILE', 2) as "MOBILE N°2"
, ('MOBILE', 3) as "MOBILE N°3"
) ); |
Partager