Discussion :

[Médiat]

Question souvent posée :
J'ai des vendeurs qui travaillent sur plusieurs villes, si j'interroge ma base, j'obtiens :

Code :

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

1
2
3
4
5
6
7
8
9
1	Paris
1	Lille
1	Lyon
1	Marseille
1	Bordeaux
2	Paris
2	Lyon
2	Brest
3	Paris

je voudrais :

Code :

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

1
2
3
3	Paris
2	Brest;Lyon;Paris
1	Bordeaux;Lille;Lyon;Marseille;Paris

Cette question est souvent rejeté au rang de cosmétique, personnellement, j'ai tendance à penser (hors tous problèmes de performance) qu'il s'agit d'une fonction agrégative au même titre que SUM ou AVG (mais avec des problèmes spécifiques de taille, d'ordre, etc...).

Voici une solution avec le WITH RECURSIVE de la norme SQL (en fait il n'y a pas le "RECURSIVE", puisque SQLExpress 2005 supporte la fonction, mais pas le mot clé )

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
create table Territoire (IdVendeur integer, Ville varchar(20))
 
insert into Territoire values (1, 'Paris');
insert into Territoire values (1, 'Lille');
insert into Territoire values (1, 'Lyon');
insert into Territoire values (1, 'Marseille');
insert into Territoire values (1, 'Bordeaux');
insert into Territoire values (2, 'Paris');
insert into Territoire values (2, 'Lyon');
insert into Territoire values (2, 'Brest');
insert into Territoire values (3, 'Paris');
 
 
 
with px (idVendeur, Villes, Precedente, Niveau) as
     (   select IdVendeur, cast(Ville as varchar(MAX)), cast (ville as varchar(MAX)), 1
         from   Territoire a
         where  Ville = (select min(Ville) from Territoire b where a.idVendeur = b.idVendeur)
      union all
         select a.IdVendeur, px.Villes + ';' + a.ville, cast(a.Ville as varchar(max)), Niveau + 1
         from Territoire a inner join px on a.idVendeur = px.idVendeur and a.Ville > px.Precedente
     )
select IdVendeur, Villes
from px a
where Niveau = (select max(Niveau) from px b where a.idVendeur = b.idVendeur)

Attention, cette solution est sans doute assez mauvaise en terme de performance si le nombre de "Villes" est élevé.

Je posterais une solution pour ORACLE et le CONNECT BY d'ici peu dans dans ce même fil, j'ai une requête qui marche, mais je veux "l'améliorer"

[Xo]

Excellente initiative, si je puis me permettre ! Le sujet est en effet assez régulièrement abordé, et il est clair que de proposer une solution technique (tout en mettant en garde côté performances) me paraît une bonne idée.

Je n'ai jamais abordé cette problématique sous Oracle, j'attends donc ta prochaine intervention sur ce point avec impatience (mais je saurais attendre la fin du fignolage, chut !, ne dérangeons pas l'artiste )

[SQLpro]

C'est effectivement une solution que j'ai donné dans mon article sur la récursivité dans SQL : http://www.sqlservercentral.com/columnists/fBROUARD/recursivequeriesinsql1999andsqlserver2005.asp
Il va être publié en français en novembre dans SQL Server Magazine à l'occasion de la sortie de SQL Server 2005.

Bien entendu il sera publié simultanément sur mon site !

A +

[Médiat]

Avec les mêmes données (et les mêmes risque au niveau des performances) que pour la première solution, voici un exemple pour ORACLE :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT idVendeur, Liste
FROM (SELECT idVendeur, Ville, PRIOR Ville, LEVEL AS niveau, sys_connect_by_path(Ville, ',') AS Liste
      FROM Territoire a
      CONNECT BY idVendeur = PRIOR idVendeur AND Ville > PRIOR Ville
      START WITH (idVendeur, Ville) IN (SELECT idVendeur, Min(Ville) FROM Territoire GROUP BY idVendeur)
     ) b
WHERE niveau = (SELECT Max(Niveau) 
                FROM (SELECT idVendeur, Ville, PRIOR Ville, LEVEL AS niveau, sys_connect_by_path(Ville, ',') AS Liste
                      FROM Territoire a
                      CONNECT BY idVendeur = PRIOR idVendeur AND Ville > PRIOR Ville
                      START WITH (idVendeur, Ville) IN (SELECT idVendeur, Min(Ville) FROM Territoire GROUP BY idVendeur)
                     ) a 
                WHERE a.idVendeur = b.idVendeur
               );

sys_connect_by_path est "officiel" depuis la 9i, mais fonctionne depuis la 8i (undocumented, donc à éviter dans un environnement de production )

On voit bien l'intérêt du WITH sur cet exemple