Introduction

Les enregistrements des tables nécessitent parfois d'être retournées de manières hierarchiques :

TSQL permet toute sorte de manipulations hierarchiques.

Pour cela, 2 moyens s'offrent à nous :

1. Utiliser une colonne de type HierarchyID qui stock la position hyerarchique.
   (Les requêtes deviendront alors beaucoup plus simples et optimisées).
2. Utiliser une colonne qui stock la position hyerarchique sous forme d'identifiants.
   (La méthode traditionnelle)

Avec HierarachiID

Le type de données HierarchiID est intégralement conçu pour
manipuler les hyerachies de données au sein des tables.

Il stocke l'information de manière similaire à varbinary,
tout deux stoquent en binaire les données.

Voici comment l'utiliser.

1. On commence par créer notre table :

        
CREATE TABLE employe
(
  emp_id int PRIMARY KEY NOT NULL,
  emp_hy hierarchyid NOT NULL,
  emp_nom varchar(40) NOT NULL,
  emp_pre varchar(40) NOT NULL,
  emp_titre varchar(40) NOT NULL
);
        
  

2. On y insère les données suivantes :

Ici, on insère directement sous forme d'une chaine de caractères l'emplacement
hyerachique. Une conversion implicite est faite automatiquement.

3. Finalement, on récupère les donneés :

Là, on convertit vers un type texte afin qu'il soit lisible, autrement des données hexadécimales s'afficheront.
La méthode .ToString() qui sera expliquée plus bas permet d'arriver au même résultat.

Résultat de la requête :

Méthodes

Quelques méthodes parmis les plus pratiques :

• .ToString()
  Converti le nœud en chaine de charactères
  SELECT emp_hy.ToString(), emp_pre WHERE emp_nom = 'Point'
  Résultat req. : /1/, Pierre
• HierarchiID::GetRoot()
  Retourne le nœud racine.
  SELECT emp_hy.ToString(), emp_pre WHERE emp_hy = HierarchiID::GetRoot()
Résultat req. : /1/, Pierre
• .GetLevel()
  Retourne le niveau d'un noeud
  SELECT emp_hy.GetLevel() AS Level, emp_id, emp_nom, emp_pre, emp_titre FROM employe ORDER BY emp_hy
Résultat :

  #	Level	emp_id	emp_nom	emp_pre	emp_titre
  1	1	1	Point	Pierre	PDG
  2	2	8	Moine	Klein	Directeur Marketing
  3	3	2	Andre	Roger	Assistant Marketing
  4	3	3	Vil	Mark	Assistant Marketing
  5	2	7	Clon	Marie	Directrice RH
  6	3	5	Tant	Elise	Assistante RH

Liste complète des méthodes :
https://docs.microsoft.com/fr-fr/sql/t-sql/data-types/hierarchyid-data-type-method-reference?view=sql-server-ver15

Requête sur plusieurs tables

Soit, les 3 tables suivantes :

Requête à executer :

SELECT vio.vio_hy.ToString() AS Level, vio.vio_nom, vio.vio_niveau, vln.vln_nom, vln.vln_pre
FROM violon AS vio JOIN
jouer AS j ON j.vio_id = vio.vio_id JOIN
violoniste AS vln ON j.vln_id = vln.vln_id ORDER BY vio.vio_hy


Résultat :

Cheat-sheet

Voici une feuille d'aide que j'ai pu conceptualiser lors de mon stage.
Celle-ci regroupe toutes sortes de manipulations liées aux hyerarchies.

        -- Ajout d'une colonne hiérarchique dans une table
        Alter table USINE ADD USI_H hierarchyid

        -- Mis à jour d'un enregistement
        UPDATE dbo.USINE SET USI_H = '/1/' WHERE USI_ID = 38

        -- Comparaison
        SELECT USI_H.ToString() FROM USINE WHERE [USI_H] < '/3/'
        SELECT USI_H.ToString() FROM USINE WHERE [USI_H] > '/2/1/'



        /* Récupèrer l'élément parent */
        SELECT USI_ID, USI_NOM, USI_H.ToString() from USINE where (
            SELECT USI_H.GetAncestor(1) as parent FROM USINE WHERE USI_ID = 68
        ) = USI_H


        /* Récupèrer l'élément + les éléments enfants */
        SELECT USI_ID, USI_NOM, USI_H.ToString() from USINE WHERE USI_H.IsDescendantOf(
            (SELECT USI_H.ToString() from USINE WHERE USI_ID = 40)
        ) = 1


        /* Récupèrer l'élément + tous les éléments parents */
        SELECT t1.USI_H.ToString() as Path, t1.USI_ID
        FROM (SELECT * FROM USINE WHERE USI_ID = 68) t2,
        USINE t1
        WHERE t2.USI_H.IsDescendantOf(t1.USI_H) = 1 ORDER BY Path

        /* Récupère tous les éléments d'un niveau précis */
        SELECT USI_ID, USI_NOM, USI_H.ToString() FROM USINE WHERE USI_H.GetLevel() = 1



        --=======================================================================================
        -- Retourne le future parent niveau 1 de la hiérarchie
        -- Ex: Si le dernier noeud d'une hierarchie est /107/6/ : retourne /108/
        --                                                /16/    : retourne /17/
        --                                                /10/1/2/: retourne /11/
        --
        --=======================================================================================

        SELECT top 1 hierarchyid::GetRoot().GetDescendant(t1.hyer ,null).ToString() as hyer from (

            SELECT top 1 t0.hyer.GetAncestor(t0.niv - 1) as hyer from (

                SELECT top 1 USI_H.GetLevel() as niv, USI_H as hyer from USINE order by USI_H desc

            ) t0

        ) t1, USINE



        --=======================================================================================
        -- Retourne le future frère du dernier enregistrement de la hiérarchie
        -- Ex: Si le dernier noeud est    /10/1/4/    :    retourne /10/1/5/
        --                                /2/2/        :    retourne /2/3/
        --                                /20/        :    retourne /21/
        --
        -- Possibilité de changer le noeud de départ en changant la condition 1=1
        --=======================================================================================
        SELECT top 1 T1.hyer.GetAncestor(1).GetDescendant(T1.hyer ,null).ToString() AS hyer FROM (
            SELECT top 1 USI_H as hyer from USINE where 1=1 order by hyer desc
        ) T1, USINE order by USI_H desc




        --============================================================================================================
        -- Retourne le future dernier fils d'un enregistrement de la hiérarchie
        -- Ex: Si le noeud noeud est    /10/1/    ET que le dernier fils de ce noeud est /10/1/7    :    retourne /10/1/8/
        --                                /2/2/3/    ET que le dernier fils de ce noeud est /2/2/3/4    :    retourne /2/2/3/5
        --                                /20/    ET que le dernier fils de ce noeud est /20/3    :    retourne /20/4/
        --
        -- Possibilité de changer le noeud de départ en changant la condition 1=1
        --===========================================================================================================

        SELECT top 1 T1.hyer.GetAncestor(1).GetDescendant(T1.hyer ,null).ToString() AS hyer FROM (
            SELECT top 1 USI_H as hyer from USINE WHERE USI_H.IsDescendantOf(
            (
                SELECT USI_H from USINE WHERE USI_ID = 35
            )
            ) = 1 order by hyer desc
        ) T1, USINE order by USI_H desc
    

Traduction d'une requête Oracle (PL/SQL) vers TSQL

Avec Colonne ID

La 2ᵉ méthode consiste à utiliser une colonne identifiant, de type INT
et de faire une boucle afin de remonter à l'enregistrement parent.

Voici un exemple :

        
        WITH descendant AS (

        SELECT EMPLOYE.EMP_ID,
        EMPLOYE.EMP_MAN_ID,
        EMPLOYE.EMP_NOM,
        EMPLOYE.EMP_PRE,
        EMPLOYE.EMP_TITRE,
        LEFT(CONCAT(CONVERT(nvarchar(20), 1), ' - ', EMPLOYE.EMP_TRI),20) AS ordre
        FROM EMPLOYE
        WHERE EMPLOYE.EMP_MAN_ID IS null

        UNION ALL

        SELECT EMPLOYE.EMP_ID,
        EMPLOYE.EMP_MAN_ID,
        EMPLOYE.EMP_NOM,
        EMPLOYE.EMP_PRE,
        EMPLOYE.EMP_TITRE,
        LEFT(CONCAT(descendant.ordre , ' - ', EMPLOYE.EMP_TRI),20) AS ordre
        FROM EMPLOYE, descendant
        WHERE descendant.EMP_ID = EMPLOYE.EMP_MAN_ID

        )

        SELECT descendant.EMP_ID,
        descendant.EMP_MAN_ID AS ID_PERE,
        descendant.EMP_NOM,
        descendant.EMP_PRE,
        descendant.EMP_TITRE,
        descendant.ordre AS NIVEAU
        FROM descendant
        ORDER BY NIVEAU ASC