Scinder les éléments d'une colonne en plusieurs colonnes

**fabcd31** · 21/01/2020, 14h28

Bonjour,

Je travaille sous ORACLE (SQL à la carte WEB-i) et j'utilise une requête qui me remonte entre autre une liste de numéro de téléphone. Pour un individu donné, celui-ci peut avoir plusieurs numéro (FIXE et,ou MOBILE)

Exemples:

ID_INDIVIDU	NUM_COORDO
12554	065421…
12554	072122…
12554	051266…
149962	014487…
149962	068911…

Voici la requête de base

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
SELECT 
information.ID_INDIVIDU,
contact.NUM_COORDO
FROM 
contact 
left outer JOIN information ON information.id_information = contact.id_information

Ma question est la suivante, comment puis-je faire pour scinder au moment de la création de la requête la colonne NUM_COORDO en une colonne NUM_COORDO_MOBILE (regroupant les 06-07) et une colonne NUM_COORDO_FIXE (regroupant les 01-02-03-04-05-08-09), comme suis:

ID_INDIVIDU	NUM_COORDO_MOBILE	NUM_COORDO_FIXE
12554	065421…	051266…
12554	072122…
149962	068911…	014487…

Je suis sur ce problème depuis des semaines, j'ai farfouillé sur le site, le web sans résultat.

Je me décide enfin, à vous solliciter.
Comme c'est la première fois, je ne maitrise pas toutes les ficelles du site et code de dialogue. J'espère avoir était clair.

Merci d'avance pour votre aide

**escartefigue** · 21/01/2020, 14h51

bonjour,

C'est possible, voici un lien qui propose plusieurs solutions pour ce besoin
https://fadace.developpez.com/mssql/pivot/

**escartefigue** · 21/01/2020, 14h59

Voici un exemple de solution possible :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
with CO_contact (CO_id, CO_nom, CO_prenom)
  as (select 001, 'Dupont    ', 'Marcel   '  from dual union all
      select 002, 'Ben Sousan', 'Fatima   '  from dual union all
      select 003, 'Mac Gregor', 'Kilian   '  from dual union all
      select 004, 'Da Silva  ', 'Maria    '  from dual 
     )
  ,  IN_info (CO_id, IN_id, IN_numero, IN_type)
  as (select 001, 001, '0135456688', 'FD'  from dual union all
      select 001, 002, '0177889960', 'FW'  from dual union all
      select 002, 001, '0614151617', 'PP'  from dual union all
      select 004, 001, '0666771515', 'PW'  from dual union all
      select 004, 002, '0699887766', 'PP'  from dual union all
      select 004, 003, '0699151555', 'FD'  from dual
     )
select CO.CO_nom
     , CO.CO_prenom
     , I1.IN_numero
     , case when I1.IN_type = 'FD' then 'Fixe domicile'
            when I1.IN_type = 'FW' then 'Fixe travail'
       end Tfixe
     , I2.IN_numero       
     , case when I2.IN_type = 'PP' then 'Mobile perso'
            when I2.IN_type = 'FW' then 'Mobile travail'
       end Tport
from CO_contact  CO
left join IN_info  I1
  on I1.CO_id = CO.CO_id
 and I1.IN_type like 'F%'
left join IN_info  I2
  on I2.CO_id = CO.CO_id
 and I2.IN_type like 'P%' 
;

Ce qui donne :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
CO_NOM	CO_PRENOM	IN_NUMERO	TFIXE	IN_NUMERO	TPORT
Da Silva  	Maria    	0699151555	Fixe domicile	0699887766	Mobile perso
Da Silva  	Maria    	0699151555	Fixe domicile	0666771515	
Ben Sousan	Fatima   			0614151617	Mobile perso
Mac Gregor	Kilian   				
Dupont    	Marcel   	0177889960	Fixe travail		
Dupont    	Marcel   	0135456688	Fixe domicile

Notez qu'avec une solution sur deux colonnes, dans le cas où une personne a plusieurs téléphones de même type (au sens plusieurs fixes ou plusieurs mobiles), un seul d'entre eux est récupéré.

**fabcd31** · 22/01/2020, 07h49

Bonjour Escartefigue,

Je te remercie infiniment d'avoir pris le temps de répondre

.
Je pensais bien qu'il y avait une histoire de requête pivot.
Je m'en vais de ce pas tester ta solution.

Bonne journée

**escartefigue** · 22/01/2020, 11h35

Oups, je me rend compte d'une coquille de copier-coller dans la requête que j'ai proposée, il faut bien sûr remplacer

when I2.IN_type = 'FW' then 'Mobile travail'.

par

when I2.IN_type = 'PW' then 'Mobile travail'.

sinon on utilise le même code que celui du fixe travail

**fabcd31** · 22/01/2020, 13h19

Merci pour cette indication d'erreur, pas d’inquiétude j'avais repéré la coquille.

J'ai adapté ta proposition à mon cas, en effet, je ne fais pas la distinction entre fixe travail et ou fixe domicile / mobile perso et ou mobile travail.
Je traite les éléments par type d'indicatif:
- Mobile (06 / 07)
- Fixe (01 / 02 / 03 / 04 / 05 / 08 / 09)
Je tiens compte surtout de ta remarque concernant sur la remontée des individus possédant plus de deux numéros de téléphones

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT 
INFO.matricule
,Mobile1.numtel
,Mobile2.numtel
,Mobile3.numtel
,Fixe.numtel
 
FROM 
information INFO
left outer JOIN contact Mobile1 ON Mobile1.id_information =INFO.id_information
and Mobile1.numtel like '06%' or Mobile1.numtel like '07%'
left outer JOIN contact Mobile2 ON Mobile2.id_information =INFO.id_information
and Mobile2.numtel like '06%' or Mobile2.numtel like '07%'
left outer JOIN contact Mobile3 ON Mobile3.id_information =INFO.id_information
and Mobile3.numtel like '06%' or Mobile3.numtel like '07%'
left outer JOIN contact Fixe ON Fixe.id_information =INFO.id_information
and Fixe.numtel like '01%' or Fixe.numtel like '02%' or Fixe.numtel like '03%' or Fixe.numtel like '04%' or Fixe.numtel like '05%' or Fixe.numtel like '08%' or Fixe.numtel like '09%'

Seulement je pense que l'utilisation massive du LIKE, fait planter le système. La requête tourne, mais le temps de traitement étant trop long j'ai un retour d'erreur.

**escartefigue** · 22/01/2020, 13h30

plusieurs problèmes dans cette requête

il manque les parenthèses dans la combinaison and/or de la dernière jointure ce qui explique les temps de réponse très longs
les 3 premières jointures sont redondantes, elles sont donc inutiles. Il faut enrichir le critère s'il existe plusieurs mobiles pour ne pas extraire 3 fois le même

Pour la dernière jointure, ça donne

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
left outer JOIN contact Fixe 
  ON Fixe.id_information =INFO.id_information
 and (    Fixe.numtel like '01%' 
      or  Fixe.numtel like '02%' 
      or  Fixe.numtel like '03%' 
      or  Fixe.numtel like '04%' 
      or  Fixe.numtel like '05%' 
      or  Fixe.numtel like '08%' 
      or  Fixe.numtel like '09%')

Ou, plus simple

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
left outer JOIN contact Fixe 
  ON Fixe.id_information =INFO.id_information
 and Fixe.numtel not like '06%'
 and Fixe.numtel not like '07%'

**fabcd31** · 23/01/2020, 08h29

Bonjour,

Merci pour tes remarques, qui me font avancer à grand pas. Effectivement les mêmes numéros apparaissent dans les trois colonnes, j'essaie de mettre des conditions, mais rien n'y fait.

Si cette redondance peut être éviter, comment puis-je faire pour créer les trois colonnes (Mobile1.numtel; Mobile2.numtel, Mobile3.numtel).

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 SELECT 
INFO.matricule
,Mobile1.numtel
,Mobile2.numtel
,Mobile3.numtel
,Fixe.numtel
 
FROM 
information INFO
left outer JOIN contact Mobile1 ON Mobile1.id_information =INFO.id_information
 and (Mobile1.numtel like '06%')
left outer JOIN contact Mobile2 ON Mobile2.id_information =INFO.id_information
 and (Mobile2.numtel like '07%')
left outer JOIN contact Mobile3 ON Mobile3.id_information =INFO.id_information
 and (Mobile3.numtel like '06%' or  Mobile3.numtel like '07%')
left outer JOIN contact Fixe ON Fixe.id_information =INFO.id_information
and Fixe.numtel not like '06%'
and Fixe.numtel not like '07%'

Je ne peux pas faire une colonne '06' et une colonne '07', si un individu possède de numéro en '06' ils ne remonteront pas. J'ai tenté avec La condition distinct mais sur des doublons par colonne, je n'y parviens pas.

J'avoue que je sèche.

**escartefigue** · 23/01/2020, 15h06

voici une solution :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
 with TBMOB (CO_id, IN_id, nom, prenom, TMOB, RMOB)     
  as (select CO.CO_id
           , TL.IN_id
           , CO.CO_nom
           , CO.CO_prenom
           , TL.IN_numero
           , row_number()
             over(partition by CO.CO_id
                  order by TL.IN_numero)           
      from  CO_contact CO
      inner join IN_info TL
         on TL.CO_id = CO.CO_id
        and (    TL.IN_numero like '06%'
             or  TL.IN_numero like '07%')
     )        
select T1.nom
     , T1.prenom
     , t1.tmob
     , t2.tmob
     , t3.tmob
from tbmob T1
left outer join tbmob T2
  on t2.CO_id = T1.CO_id  
 and t2.rmob = 2
left outer join tbmob T3
  on t3.CO_id = T1.CO_id  
 and t3.rmob = 3 
where t1.rmob = 1
order by t1.nom
       , t1.prenom
;

J'ai perdu du temps pour la tester : comme un imbécile j'avais utilisé l'alias IN pour la table IN_info oubliant que c'était un mot réservé

(faut avouer aussi que les messages d'erreur Oracle sont pas toujours très précis

)

**fabcd31** · 24/01/2020, 13h53

Bonjour,

Merci pour ton acharnement à résoudre mon problème.
Ça fait du bien de voir que dans ce monde de hyperconnecté où l’individualisme prime parfois plus que l'entraide, il y aura toujours des groupes ou des communautés qui milite pour un partage des connaissances

Comme je suis assez chargé en cette fin de semaine, je m'y remets lundi.

Effectivement les message d'erreurs d'oracle sont particulièrement abscons parfois.

Je te tiens au courant, bon week-end et encore mille

**aieeeuuuuu** · 24/01/2020, 15h27

Bonjour,

une alternative qui évite les multiples jointures (pas testée)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
WITH ContactType AS (
	SELECT *, CASE WHEN NUM_COORD LIKE '06%' OR NUM_COORD LIKE '07%' THEN 'M' ELSE 'F' END AS TypeCoord
	FROM contact
 
)
,ContactRN AS (
	SELECT *, ROW_NUMBER() OVER(PARTITION BY id_information , TypeCoord ORDER BY NUM_COORDO ) AS RN
	FROM ContactType
)
SELECT 
		I.ID_INDIVIDU
	,	MAX(CASE WHEN TypeCoord = 'M' AND RN = 1 THEN C.NUM_COORDO END) AS Mobile1
	,	MAX(CASE WHEN TypeCoord = 'M' AND RN = 2 THEN C.NUM_COORDO END) AS Mobile2
	,	MAX(CASE WHEN TypeCoord = 'M' AND RN = 3 THEN C.NUM_COORDO END) AS Mobile3
	,	MAX(CASE WHEN TypeCoord = 'F' AND RN = 1 THEN C.NUM_COORDO END) AS Fixe1
	,	MAX(CASE WHEN TypeCoord = 'F' AND RN = 2 THEN C.NUM_COORDO END) AS Fixe2
FROM	information I
LEFT OUTER JOIN ContactRN C
	ON C.id_information I.id_information
GROUP BY I.ID_INDIVIDU

**fabcd31** · 27/01/2020, 08h28

Bonjour aieeeuuuuu,

Merci pour ta collaboration, à ce post.

Je testerai également ta version.

Si j'avais su que vous prendriez tous autant de temps à analyser et résoudre mon problème, j'aurais envoyer ma demande il y a bien longtemps.

Je reviens vers vous très vite, dès les tests effectués.

Merci encore et bonne journée

**fabcd31** · 28/01/2020, 15h52

Je souhaite tout d’abord remercier les personnes qui m'ont aider à résoudre mon casse tête.

Je désespérais trouver la solution un jour.

Afin que cette résolution puisse servir à tous, je partagerai donc les éléments pour qui souhaiterai récupérer la procédure.
Le requête initiale est celle-ci:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
 
SELECT
  INFORMATION.MATRICULE,
  CONTACT.NUMTEL
FROM
  CONTACT INNER JOIN INFORMATION ON (INFORMATION.ID_INFORMATION=CONTACT.ID_INFORMATION)
 
WHERE
  ( INFORMATION.MATRICULE  Is Not Null  AND CONTACT.NUMTEL  Is Not Null )

Le résultat étant sous la forme:

MATRICULE	NUMTEL
12554	065421…
12554	072122…
12554	051266…
149962	014487…
149962	068911…

La requête finale que j'ai réussi à élaborer grâce au concours des différents membres, se présente sous cette forme:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
 
with TBMOB (id_information, matricule, numtel, TMOB, RMOB)   
 
  as (select INF.id_information
           , INF.matricule
           , Fixe.numtel
           , MOB.numtel
           , row_number() over(partition by INF.id_information order by MOB.numtel)           
      from  INFORMATION INF
            left outer join CONTACT MOB on MOB.id_information = INF.id_information and (MOB.numtel like '06%' or  MOB.numtel like '07%')
            left outer join CONTACT Fixe ON Fixe.id_information =INF.id_information and Fixe.numtel not like '06%'  and Fixe.numtel not like '07%'
      Where (INF.matricule is not null and (MOB.numtel is not null or Fixe.numtel is not null))
     )        
select T1.matricule
     , T1.numtel "FIXE"
     , T1.tmob "MOBILE N°1"
     , T2.tmob "MOBILE N°2"
 
from  TBMOB T1
      left outer join TBMOB T2 on T2.id_information = T1.id_information and (T2.rmob = 2 and T1.tmob != T2.tmob)
where 
      T1.rmob = 1 order by T1.matricule

La table finale étant dorénavant sous cette forme (ce que je cherchait)

MATRICULE	FIXE	MOBILE N°1	MOBILE N°2
12554	051266…	065421…	072122…
149962	014487…	068911…

Qu'en pensez-vous ?
Moi je suis ravi

J'espère que ce post pourra servir à d'autre, c'est le but.
Merci encore

**escartefigue** · 28/01/2020, 19h33

Merci à toi d'avoir publié la solution qui te convient et d'avoir pensé à mettre à "résolu"

**Waldar** · 29/01/2020, 15h46

Si vous ne cherchez que deux numéros de mobile au maximum vous pouvez faire comme ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
    select inf.matricule
         , max(case when substr(ctc.numtel, 1, 2) not in ('06', '07') then ctc.numtel end)          as "FIXE"
         , min(case when substr(ctc.numtel, 1, 2)     in ('06', '07') then ctc.numtel end)          as "MOBILE N°1"
         , nullif( max(case when substr(ctc.numtel, 1, 2)     in ('06', '07') then ctc.numtel end) 
                 , min(case when substr(ctc.numtel, 1, 2)     in ('06', '07') then ctc.numtel end)) as "MOBILE N°2"
      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
  group by inf.matricule
  order by inf.matricule asc;

**Waldar** · 29/01/2020, 16h00

Et si vous voulez quelque chose d'un peu plus souple, mais pas encore complètement dynamique, vous pouvez utiliser un PIVOT :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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"
                                           ) );

Ici je me suis limité à 2 fixe et 3 mobiles.

**fabcd31** · 30/01/2020, 09h31

Bonjour Waldar,

Merci pour ces deux nouvelles propositions, Je n'en demandais pas temps

A la lecture de celles-ci, je vois a quel point mon niveau en SQL est à des années lumières de certains membres, là je dis totale respect

Vos contributions vont m'aider à avancer à grand pas.

Concernant la première requête, elle est parfaite (je valide) puisqu'elle me remonte les mêmes valeurs que celle utilisées dorénavant (merci à vous).
Il me reste à comprendre la démarche et sa construction. J'ai du boulot !!!!

Concernant la deuxième, il y a comme un couac.
Lorsque je la lance, les résultat sont quelques peu... réduit, seulement 4 matricule au lieu des 33600

.

Voici le résultat:

MATRICULE	FIXE N°1	FIXE N°2	MOBILE N°1	MOBILE N°2	MOBILE N°3
14874646		05783..
156486453	05221..
566864					065412..
78944			062211…	062211…

Je vais essayer de comprendre la philosophie de la requête pivot (lire le post sur le site) pour voir d'où viens le problème.

Bonne journée

**aieeeuuuuu** · 30/01/2020, 10h47

Bonjour

Dans la deuxième requete de Waldar, je pense qu'il manque juste une clause de partitionnement par matricule.

donc remplacer

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

, 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)

par

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

, row_number() over(partition by inf.matricule, case when substr(ctc.numtel, 1, 2) in ('06', '07') then 'MOBILE' else 'FIXE' end order by ctc.numtel asc)

**Waldar** · 30/01/2020, 11h26

Absolument ! Bien vu aieeeuuuuu.

**fabcd31** · 30/01/2020, 14h53

Bonjour aieeeuuuuu

Merci pour ton intervention, bonne remarque.
Je n'avais pas eu le temps de regarder et j'aurais certainement mis du temps à trouver

Waldar,

Grâce à cette requête (pivot) tu as sans le savoir trouver une solution à un autre problème qui m'est apparu en fusionnant les résultats de deux requête sous WEBi.

Scinder les éléments d'une colonne en plusieurs colonnes

Langage SQL

Discussions similaires

Partager

Partager