Discussion :

[emmadesp]

Bonjour,
J'ai un besoin particulier, Je dispose d'une table arbre (en représentation intervallaire)
Mon besoin est un peu particulier, je cherche à représenter l'arbre "à plat" ie avec autant de colonne que de niveau et autant de ligne que de feuille.
Ma table a un peu ce dessin :

Id
Libellé
Niveau
Borne_Gauche
Borne_Droite

Si le nombre de Niveau est 3, la représentation serait :

Libellé_Niveau_1,Libellé_Niveau_2,Libellé_Niveau_3,Id_de_la_feuille

Je sais bien qu'à partir d'un certain nombre de niveaux cela ne sera plus "jouable", mais le nombre de niveaux devrait rester raisonnable. Le problème c'est qu'il doit être dynamique.
Ce qui sous entend que les utilisateurs doivent pouvoir ajouter un niveau et la requête doit continuer de fonctionner.

Le jeu est donc d'avoir une requête générique produisant le résultat souhaité.
J'ai bien pensé aux CTE, mais j'ai l'impression que cela ne colle pas. Idem pour le PIVOT.

Je ne sais pas si je suis clair.
Merci d'avance
Et à la disposition de tous pour de plus amples informations.

Merci d'avance

[aieeeuuuuu]

Bonjour,

J'ai bien pensé aux CTE, mais j'ai l'impression que cela ne colle pas. Idem pour le PIVOT.

Ce que vous decrivez est bien un PIVOT.
Mais il faut avoir un nombre défini de colonnes pour le résultat. Dans le cas contraire, il faudra faire en SQL dynamique.

Il serait bien préférable de laisser à votre applicatif la charge d'effectuer ce pivot.

[Mat.M]

bonjour je suis pas trop spécialiste de Tranasact-SQL mais avec ce langage ça devrait être possible..
on passe en paramètre le nombre de niveau et en fonction de cela avec des conditions logiques on va créer des noeuds à l'arborescence de l'arbre avec ALTER TABLE .
Id_Feuille représentera alors Id_Feuille parent c.a.d. qu'au niveau 3 Id_feuille sera 2.
Maintenant qu'elle est l'intérêt de représenter une arborescence dans une table de base de données ?

[emmadesp]

Maintenant qu'elle est l'intérêt de représenter une arborescence dans une table de base de données ?

Bonjour,
L'intérêt est évident.
Dans certains métiers il y a des relations à profondeur variable suivant le contexte...
Rapidement; les notions de familles de produits (sous-famille, famille, groupe de famille, schémas, univers) qui peuvent être variables suivant le contexte. La grande distribution est friande de ce genre de regroupement, mais chacun a sa propre codification avec un nombre de niveau variable, si vous voulez travailler avec le plus grand nombre, il faut gérer les spécificité de chacun...
Les notions de regroupement géographique en sont un autre exemple, tout le monde n'a pas besoin de travailler avec un niveau d'agrégation aussi fin que le bénélux, mais...
Les marques, sous marques etc....
Je pourrais en citer des tonnes

La programmation des arborescences est complexe
Mon objectif est de généraliser les traitements pour fournir à mes développeurs des structures normalisées prêtes à l'emploi et quel que soit le contexte. Donc la base de données est le candidat idéal...

Au passage merci à SQLPRO pour ses publications sur le sujet.

[SQLpro]

Sur la base de cet exemple :

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
33
34
35
36
37
38
39
40
41
CREATE TABLE NEW_FAMILLE
(NFM_BG  INTEGER,
 NFM_BD  INTEGER,
 NFM_LIB VARCHAR(16))
GO 
 
INSERT INTO NEW_FAMILLE (NFM_BG, NFM_BD, NFM_LIB)       VALUES (1, 44, 'Transport')
INSERT INTO NEW_FAMILLE (NFM_BG, NFM_BD, NFM_LIB)       VALUES (2, 21, 'Aérien')
INSERT INTO NEW_FAMILLE (NFM_BG, NFM_BD, NFM_LIB)       VALUES (3, 4, 'Planeur')
INSERT INTO NEW_FAMILLE (NFM_BG, NFM_BD, NFM_LIB)       VALUES (5, 6, 'Parachute')
INSERT INTO NEW_FAMILLE (NFM_BG, NFM_BD, NFM_LIB)       VALUES (7, 8, 'Hélico')
INSERT INTO NEW_FAMILLE (NFM_BG, NFM_BD, NFM_LIB)       VALUES (9, 10, 'Fusée')
INSERT INTO NEW_FAMILLE (NFM_BG, NFM_BD, NFM_LIB)       VALUES (11, 12, 'ULM')
INSERT INTO NEW_FAMILLE (NFM_BG, NFM_BD, NFM_LIB)       VALUES (13, 20, 'Avion')
INSERT INTO NEW_FAMILLE (NFM_BG, NFM_BD, NFM_LIB)       VALUES (14, 15, 'Militaire')
INSERT INTO NEW_FAMILLE (NFM_BG, NFM_BD, NFM_LIB)       VALUES (16, 17, 'Tourisme')
INSERT INTO NEW_FAMILLE (NFM_BG, NFM_BD, NFM_LIB)       VALUES (18, 19, 'Civil')
INSERT INTO NEW_FAMILLE (NFM_BG, NFM_BD, NFM_LIB)       VALUES (22, 35, 'Terrestre')
INSERT INTO NEW_FAMILLE (NFM_BG, NFM_BD, NFM_LIB)       VALUES (23, 24, 'Vélo')
INSERT INTO NEW_FAMILLE (NFM_BG, NFM_BD, NFM_LIB)       VALUES (25, 26, 'Voiture')
INSERT INTO NEW_FAMILLE (NFM_BG, NFM_BD, NFM_LIB)       VALUES (27, 28, 'Camion')
INSERT INTO NEW_FAMILLE (NFM_BG, NFM_BD, NFM_LIB)       VALUES (29, 34, 'Moto')
INSERT INTO NEW_FAMILLE (NFM_BG, NFM_BD, NFM_LIB)       VALUES (30, 31, 'Side-car')
INSERT INTO NEW_FAMILLE (NFM_BG, NFM_BD, NFM_LIB)       VALUES (32, 33, 'Trail')
INSERT INTO NEW_FAMILLE (NFM_BG, NFM_BD, NFM_LIB)       VALUES (36, 43, 'Marin')
INSERT INTO NEW_FAMILLE (NFM_BG, NFM_BD, NFM_LIB)       VALUES (37, 38, 'Planche à voile')
INSERT INTO NEW_FAMILLE (NFM_BG, NFM_BD, NFM_LIB)       VALUES (39, 40, 'Paquebot')
INSERT INTO NEW_FAMILLE (NFM_BG, NFM_BD, NFM_LIB)       VALUES (41, 42, 'Voilier')
GO
 
ALTER TABLE NEW_FAMILLE
   ADD NFM_NIVEAU TINYINT;
GO
 
UPDATE Tout
SET    NFM_NIVEAU = (SELECT COUNT(*)
           FROM   NEW_FAMILLE AS Tin
           WHERE  Tin.NFM_BG < Tout.NFM_BG 
		     AND  Tin.NFM_BD > Tout.NFM_BD)
FROM   NEW_FAMILLE AS Tout;
GO

Extrait de : http://sqlpro.developpez.com/cours/arborescence/

La requête statique pour 4 niveaux :

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
SELECT T0.NFM_LIB, T1.NFM_LIB, T2.NFM_LIB, T3.NFM_LIB
FROM   NEW_FAMILLE AS T0
       LEFT OUTER JOIN NEW_FAMILLE AS T1
	        ON  T1.NFM_BG     > T0.NFM_BG 
		    AND T1.NFM_BD     < T0.NFM_BD
			AND T1.NFM_NIVEAU = T0.NFM_NIVEAU + 1
	   LEFT OUTER JOIN NEW_FAMILLE AS T2
	        ON  T2.NFM_BG     > T1.NFM_BG 
		    AND T2.NFM_BD     < T1.NFM_BD
			AND T2.NFM_NIVEAU = T1.NFM_NIVEAU + 1
	   LEFT OUTER JOIN NEW_FAMILLE AS T3
	        ON  T3.NFM_BG     > T2.NFM_BG 
		    AND T3.NFM_BD     < T2.NFM_BD
			AND T3.NFM_NIVEAU = T2.NFM_NIVEAU + 1
WHERE  T0.NFM_NIVEAU  = 0

A +

[emmadesp]

La requête statique pour 4 niveaux :

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
SELECT T0.NFM_LIB, T1.NFM_LIB, T2.NFM_LIB, T3.NFM_LIB
FROM   NEW_FAMILLE AS T0
       LEFT OUTER JOIN NEW_FAMILLE AS T1
            ON  T1.NFM_BG     > T0.NFM_BG 
            AND T1.NFM_BD     < T0.NFM_BD
            AND T1.NFM_NIVEAU = T0.NFM_NIVEAU + 1
       LEFT OUTER JOIN NEW_FAMILLE AS T2
            ON  T2.NFM_BG     > T1.NFM_BG 
            AND T2.NFM_BD     < T1.NFM_BD
            AND T2.NFM_NIVEAU = T1.NFM_NIVEAU + 1
       LEFT OUTER JOIN NEW_FAMILLE AS T3
            ON  T3.NFM_BG     > T2.NFM_BG 
            AND T3.NFM_BD     < T2.NFM_BD
            AND T3.NFM_NIVEAU = T2.NFM_NIVEAU + 1
WHERE  T0.NFM_NIVEAU  = 0

A +

Bonjour,
Merci à vous pour la réponse. En fait je cherchais à faire les choses dynamiquement. Sans bâtir une requête dynamique...
Je commence à penser que ce n'est pas possible. La requête que vous présentez convient parfaitement, j'avais même déjà écris une requête similaire dynamique dans une PS.
Ayant lu que les requêtes dynamiques en PS, c'était MAL je cherchais quelque chose de plus "élégant".

Il semble que cela ne soit pas possible, tant pis.

Merci à tous pour le temps passé.

[emmadesp]

Bonjour,

Ce que vous decrivez est bien un PIVOT.
Mais il faut avoir un nombre défini de colonnes pour le résultat. Dans le cas contraire, il faudra faire en SQL dynamique.

Il serait bien préférable de laisser à votre applicatif la charge d'effectuer ce pivot.

Bonjour, merci pour votre réponse.
Je crois que je me suis mal fait comprendre excusez m'en. Bien entendu la requête sera bâtie de façon dynamique. Compte tenu que le nombre de colonnes en sortie est variable, dans le cadre d'un pivot, c'est obligatoire.
Pour l'instant je suis sur des travaux préparatoires de faisabilité. Pour des raisons d'organisation, je ne souhaite pas laisser au code opérationnel le soin de réaliser le pivotement.

J'avais exploré le pivotement ainsi

Code :

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

1
2
3
4
5
6
SELECT
       [1] as Continent
      ,[2] as Sous_Continent
      ,[3] as StructureCommerciale
  FROM T_NOMENCLATURE_NMC
  pivot (max(nmc_libelle) for nmc_niveau in ([0],[1],[2],[3])) as p ;

Le résultat ne convient pas car les relations entre les niveaux ne sont pas établies... Donc je me retrouve avec des cellules nulles sur chaque ligne, sauf la donnée traitée. Je me retrouve avec autant de ligne dans le résultat que de lignes dans la table.

Continent	Sous_Continent	StructureCommerciale
NULL	NULL	CEE
NULL	NULL	Hors CEE
NULL	Afrique de l'ouest	NULL
NULL	Europe de l'est	NULL
NULL	Europe de l'ouest	NULL
Afrique	NULL	NULL
Europe	NULL	NULL

Alors que le résultat attendu est :
Autant de lignes que de feuilles et dans les différentes colonnes l'arborescence détaillée. Je sais qu'il y aura beaucoup de redondance, mais c'est justement le résultat souhaité.

Continent	Sous_Continen	StructureCommerciale
Europe	Europe de l'ouest	CEE
Europe	Europe de l'ouest	Hors CEE
Europe	Europe de l'est	Xxxxx
Afrique	Yyyyy	Tttttt

C'est pour cela que je me suis posé la question des CTE...

J'espère avoir été plus clair.
Merci d'avance

Désolé pour la présentation mais j'ai un peu de mal avec les tableaux...