Discussion :

[mohe27]

Salut les amis,

Voilà j’ai une question concernant l’affichage du résultat d’une requête Sql depuis la vertical vers l’horizontal, je m’explique :
J’ai la table table1 contenant les informations ci-dessous :

Code :

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

1
2
3
4
5
6
 
Nom	Prenom	Num-tel
BR	Alain	555577666
BR	Alain	555344776
BS	Marc	666886655
BS	Marc	666765788

Comme vous pouvez le constater certaines informations se répètent sauf les num-tel.
Ce que Je souhaiterai faire c’est d’interroger cette table de sorte à ressortir le résultat suivant :

Code :

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

1
2
3
4
 
Nom	Prenom	Num-tel1  Num-tel2
BR	Alain	555577666 555344776
BS	Marc	666886655 666765788

Donc ressortir avec une seule ligne pour chaque Nom qui se répète avec plusieurs colonnes num-tel correspondantes.
Est-ce que c’est possible ??
Merci

[nuke_y]

2 solutions:

1) Si tu connais à l'avance le nombre de numéros de téléphones possibles il suffit de faire plusieurs alias de ta table et des les lier sur les champs communs (Nom et Prénom dans ton cas). A noter que s'il n'y a aucun moyen de les différencier (un ID par exemple) tu risques d'avoir des doublons et pour obtenir un résultat propre il va falloir utiliser des fonctions analytiques.

2) Si tu acceptes de réunir tous les numéros de téléphone dans une seule colonne, tu peux détourner une fonction XML:

Code :

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

1
2
3
4
 SELECT table1."Nom", table1."Prenom"
  rtrim(REPLACE(REPLACE(XMLAGG(XMLElement("x", table1."Num-tel") ORDER BY table1."Num-tel" ASC),'<x>', ''),'</x>', '-'),'-') "Tel List"  
    FROM table1
GROUP BY table1."Nom", table1."Prenom"

Ce qui est fondamental pour te répondre (outre le numéro de version Oracle) c'est donc de savoir s'il peut y avoir 1, 2 ou n numéros de téléphones pour 1 couple Nom + Prénom.

[yong321]

J'ai un exemple à
http://yong321.freeshell.org/oranote...ueryResult.txt

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
SQL> select * from a;
 
        ID NAME
---------- ----------
         1 David
         2 Yong
 
SQL> select * from b;
 
        ID SKILL
---------- --------------------
         1 Java
         1 SQL
         1 Excel
         2 Oracle
         2 SQL
 
SQL> with x as (select row_number() over (partition by id order by id) rn, b.* from b)
  2  select a.*,
  3   (select skill from x where a.id=x.id and x.rn=1) skill1,
  4   (select skill from x where a.id=x.id and x.rn=2) skill2,
  5   (select skill from x where a.id=x.id and x.rn=3) skill3
  6  from a;
 
          ID NAME       SKILL1     SKILL2     SKILL3
------------ ---------- ---------- ---------- ----------
           1 David      Java       SQL        Excel
           2 Yong       Oracle     SQL

[spdev666]

Un exemple avec une seule requete

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
 
WITH tab AS (Select 'BR' NOM, 'Alain' PRENOM, '555577666' NUMTEL From DUAL
Union All Select 'BR', 'Alain', '555344776' From DUAL
Union All Select 'BS', 'Marc', '666886655'  From DUAL
Union All Select 'BS', 'Marc', '666765788'  From DUAL)
Select Max(NOM) nom, Max(PRENOM) prenom, 
       Max(DECODE(ORDRE, 1, NUMTEL, Null)) TEL1, 
       Max(DECODE(ORDRE, 2, NUMTEL, Null)) TEL2
  From (Select ROW_NUMBER() OVER(Partition By NOM, PRENOM Order By NOM, PRENOM) ORDRE, NOM, PRENOM, NUMTEL From TAB)
 Group By NOM, PRENOM;
 
NOM PRENOM TEL1      TEL2
--- ------ --------- ---------
BR  Alain  555577666 555344776
BS  Marc   666886655 666765788

[nuke_y]

Oui c'est un bon exemple, attention qu'il ne se limite qu'à 3 valeurs.

Attention aussi qu'avec la structure dénormalisée que possède mohe27, il faudra retravailler les résultats pour éviter les doublons.

[remi4444]

Si t'es en 11g, tu peux aussi regarder du coté de la clause PIVOT si tu sais caractériser tes colonnes à l'avance.

Pour transformer des données colonnes en ligne, tu as aussi la clause WITHIN GROUP mais SQL ne te donnera pas la possibilité de faire un nombre variable de colonne sauf à faire une fonction qui te renvoit un sys_refcursor (mais se pose toujours le pb de son affichage et de son traitement.)

exemple d'utilisation de LISTAGG/WITHIN GROUP

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
WITH tab AS (SELECT 'BR' NOM, 'Alain' PRENOM, '555577666' NUMTEL FROM DUAL
Union ALL SELECT 'BR', 'Alain', '555344776' FROM DUAL
Union ALL SELECT 'BS', 'Marc', '666886655'  FROM DUAL
Union ALL SELECT 'BS', 'Marc', '666765788'  FROM DUAL
Union ALL SELECT 'BS', 'Marc', '666765789'  FROM DUAL)
select
   nom,prenom,
   listagg (NUMTEL, ',') WITHIN GROUP (order by NUMTEL) liste
FROM 
   tab
GROUP BY 
   nom,prenom

[mohe27]

Bonjour les amis,

et je vous remercie pour ces réponses, cependant j'aurai quelques remarques comme suit:

1ère réponse nuke_y:
ta solution est très bonne et elle prend en compte le nombre 1--n des num-tel que peut posséder un nom unique, le resultat est concaténé en une seule colonne "Tel List" avec "-" comme séparateur entre chaque num-tel, seulement mon besoin est de faire resortir le resultat des num-tel sur des colonnes distinctes.

2ème réponse yong321:
ta solution concrétise bien une sortie correcte des résultat seulement là elle fait appel à 2 tables disrinctes et dans mon cas je ne dispose que d'une seule table.

2ème réponse spdev666:ta solution est excellente seulement elle est figée sur des données precises sur ce que j'ai données en exemple dans la table table1 or celle que je compte interroger rellement contient quelques 30 000 lignes.

Donc le souhait est de faire sortir des resultats sans doublons coté "nom" et "prenom" avec autant de colonnes que necessaires pour le num-tel comme par exemple le tableau ci-dessous:

Code :

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

1
2
3
4
5
 
Nom	Prenom	Num-tel1  	Num-tel2	Num-tel3
BR	Alain	555577666	555344776	
BS	Marc	666886655	666765788	
BA	Charles	555567000	555898001	555777999

merci à tous

[remi4444]

Donc le souhait est de faire sortir des resultats sans doublons coté "nom" et "prenom" avec autant de colonnes que necessaires pour le num-tel comme par exemple le tableau ci-dessous:

Tu ne t'en sortiras à mon avis pas, à un moment le langage SQL renvoie un curseur et ce curseur a un nombre fixé de colonnes, ça fait partie du système. Tu n'auras pas de notion de "tableau croisé dynamique" comme dans Excel.

Après, tout dépend de ton interface, oracle fourni les données et l'appli cliente est chargée de les mettre en forme, il faut pas trop en demander à SQL.

Donc pour résoudre ton problème, il faut que tu donnes la finalité du truc.

Encore une fois, la solution qui s'approcherait le plus des exemples que tu donnes est d'appeler une fonction qui fa générer dynamiquement une requête et revoyer le tout dans un sys_refcursor. Le SQL*PLUS est alors capable de restituer ce curseur par une variable locale de type "refcursor"

EDIT: mais c'est quand meme une belle usine à gaz!

[mohe27]

ok remi4444,

Et si je fixais le nombre de colonnes num-tel à 5 par exemple, comment pourrais-je sortir un tel resultat selon ce que j'ai illustré plus haut ??

merci

[remi4444]

spdev666 et yong321 t'ont déjà répondu.

[mohe27]

Merci remi4444,

et merci à tout le monde, en fait j'ai pris l'exemple de spdev666 que je viens juste de reparamétrer avec les données que j'ai et ce avec requetes sur toute la table . ça marche à merveille

même si tu as un nom avec 3 num-tel et un autre avec 1 ou 2 seulement, la 3ème colonne de ce dernier en ressortira vide

merci encore l'équipe