Tree requête récursive

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 +