Tree requête récursive

[waxtaylor]

Bonjour à tous,

je rencontre un pb pour requêter dans une table (SQL 2008) du type F_NOMENCLAT dans laquelle existe 2 colonnes : AR_ref et NO_RefDet pour lesquelles il est possible que NO_RefDet = AR_ref (ce qui constitue un niveau de hiérarchie).
Le but est de "mettre à plat" un enregistrement. Pour exemple, le contenu de la table peut-être le suivant :
AR_Ref NO_RefDet
------- ----------
A B
A C
A D
D F
D G
F 1
F 2

avec un résultat attendu qui serait :
C1 C2 C3 C4
-- -- -- --
A B
A C
A D
A D F
A D F 1
A D F 2
A D G

mais la requête suivante échoue lamentablement dès la 3e colonne car elle ne fait que répéter, dans tous les cas, la 2e :

with tree (c1,c2,c3)
as (select ar_ref,no_refdet,NO_RefDet from F_NOMENCLAT union all select ar_ref,no_refdet,no_refdet from F_NOMENCLAT n
inner join tree t on t.c2=n.AR_Ref)
select * from tree

Merci de me donner vos avisés avis sur le sujet !

[SQLpro]

D'abord SQL ne permet pas une table avec un nombre de colonnes variables à génération spontanée. Donc il faut regrouper tout le "path" de nomenclature dans une même colonne.

Ensuite votre résultat n'est pas cohérent... En effet sur quel critère retenez vous telle ou telle ligne ? En particulier les lignes suivantes :

Code :

1
2
3
4
5
6
7
A B
A C
A D        ???
A D F      ???
A D F 1
A D F 2
A D G      ???

Voici une solution qui vous donne les éléments de nomenclature "au plus profond". A vous de l’adapter :

Jeu d'essais :

Code :

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE NOMENCLAT (AR_Ref VARCHAR(8), NO_RefDet VARCHAR(8));
 
INSERT INTO NOMENCLAT
VALUES 
('A', 'B'),
('A', 'C'),
('A', 'D'),
('D', 'F'),
('D', 'G'),
('F', '1'),
('F', '2');

Requête :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
WITH 
TT AS
(
SELECT ROW_NUMBER() OVER (ORDER BY AR_Ref, NO_RefDet) AS N, 
       AR_Ref, NO_RefDet, CAST(AR_Ref + ', ' + NO_RefDet AS VARCHAR(max)) AS LISTE,
       0 AS LVL
FROM   NOMENCLAT
UNION  ALL
SELECT TT.N, TT.AR_Ref, TN.NO_RefDet, LISTE +', ' + TN.NO_RefDet,
       LVL + 1
FROM   TT
       INNER JOIN NOMENCLAT AS TN
             ON TT.NO_RefDet = TN.AR_Ref
),
TF AS
(
SELECT AR_Ref, NO_RefDet, LISTE AS LISTE, N, LVL,
       RANK() OVER(PARTITION BY N ORDER BY LVL DESC) AS M
FROM   TT)
SELECT AR_Ref, NO_RefDet, LISTE 
FROM   TF            
WHERE  M = 1;

Résultat :

Code :

1
2
3
4
5
6
7
8
9
10
11
AR_Ref   NO_RefDet LISTE
-------- --------- --------------------
A        B         A, B
A        C         A, C
A        1         A, D, F, 1
A        2         A, D, F, 2
D        1         D, F, 1
D        2         D, F, 2
D        G         D, G
F        1         F, 1
F        2         F, 2

Lisez l'article que j'ai écrit sur le sujet : http://sqlpro.developpez.com/cours/s...te-recursives/

A +