[SQL] Une Parent Child en lignes/à plat en SQL avec les CTE et l’instruction PIVOT

Je vous l’avoue il y a des fois où j’aime bien céder à la tentation du nerd, celle de se lancer des petits défis inutiles de prime abord mais tellements jouissifs une fois résolus. Un de mes favoris a été la mise à plat de ParentChild, vous l’aurez deviné pour faire plaisir à SSAS.

Bref rappel: beaucoup de SI expriment les hiérarchies sur deux colonnes IdParent, IdEnfant, ce qui est convenable pour le stockage. L’ami Analysis Services éprouve quelques difficultés connues avec cette structure Parent/Enfant ou ParentChild (Basiquement une dimension construite comme cela ne générera d’aggrégats qu’au niveau du membre (All) et jamais en dessous). D’où l’envie de transformer un référentiel ParentChild en table mise à plat, i.e. où chaque niveau est représenté par une colonne.
Jetons d’abord un coup d’oeil à ma table avec la requête suivante:
select Code, CodeRattachement
from TbRef
Une Parent Child bien classique en somme…

Premier besoin: pour mettre chaque enregistrement dans la bonne colonne, nous allons avoir besoin du rang dans la hiérarchie. Ca, une CTE sait très bien le faire.

with
CteRang(Code, Rang)
as
(
select Code, 1
from TbRef
where CodeRattachement is null
union all
select TbRef.Code, CteRang.Rang+1
from TbRef
inner join CteRang
on TbRef.CodeRattachement = CteRang.Code
)
select Code, Rang
from CteRang
On a bien un calcul de rang, comme visible ici:

Bon passons aux choses sérieuses: pour avoir le bon matériel, nous allons générer toutes chaines hiérarchiques dit autrement tous les supérieurs de chaque tuple (le niveau maximal apparaitra donc non plus une seule fois mais n fois, n étant le nombre de noeuds.)

Pour cela nous allons faire une CTE non plus partant de la racine mais de la base: pour chaque noeud (membre d’ancrage avant le UNION ALL), nous allons générer tous ses supérieurs (membre récursif après le UNION ALL).
Le noeud d’ancrage a un rang issu de la CTE précédente, les noeuds récursifs le dérivent en retranchant 1 (il s’agit de supérieurs du noeud d’ancrage. De plus, on prend bien soin de garder l’ID du noeud duquel on est en train de construire la chaîne hiérarchique (Pvt dans mon cas)
with
CteRang(Code, Rang)
as
(
select Code, 1
from TbRef
where CodeRattachement is null
union all
select TbRef.Code, CteRang.Rang+1
from TbRef
inner join CteRang
on TbRef.CodeRattachement = CteRang.Code
),
CteMiseAPlat (Pvt,Rang,Code,Parent)
as
(
select ref.Code,CteRang.Rang,ref.Code,CodeRattachement
from TbRef ref
inner join CteRang
on CteRang.Code = ref.Code
union all
select cte.Pvt, cte.Rang - 1 , ref.Code,ref.CodeRattachement
from TbRef ref
inner join CteMiseAPlat cte
on cte.Parent = ref.Code
)
select *
from CteMiseAPlat
order by Pvt, Rang
Et ça marche:
Vous auriez pu vous en douter, il ne reste plus qu’à… pivoter tout cela, avec une belle instruction PIVOT. On pivote autour de mon champ Pvt, en prenant le Rang comme nom de colonne et en le remplissant avec le Code (mis dans une fonction d’agrégation bidon). On remplace donc le SELECT * précédent par:
select Pvt,[1],[2],[3],[4],[5],[6]
from
(
select Pvt, Rang, Code
from CteMiseAPlat
) as u
pivot
(
Max(Code)
FOR Rang IN ([1],[2],[3],[4],[5],[6])
) as t
Ce qui donne, tadaaaa:
Voilà! La performance n’est pas excellentissime (mieux vaudrait sûrement précalculer les rangs), mais je trouve que c’est un joli couplage de CTE et de PIVOT pour un usage très commun.
A bientôt!

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s