Des données hiérarchiques dans SQL SERVER

Pourquoi créer une nouvelle table ?
Vous pouvez calculer cette information à la volée :
Code:
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
With MaTable ([CODE_Etablissement], [LIB_Etablissement], [CODE_ETABLISSEMENT_MERE]) as
(
select 5, 'DIRECTION GÉNÉRALE' , NULL union all
select 4, 'DIRECTION RH'       , 5    union all
select 3, 'DIRECTION MARKETING', 5    union all
select 2, 'DIRECTION SERVICE'  , 4    union all
select 1, 'DIRECTION SERVICE2' , 4
)
  ,  CTEr ([CODE_Etablissement], [LIB_Etablissement], [CODE_ETABLISSEMENT_MERE], [LEVEL]) as
(
select [CODE_Etablissement], [LIB_Etablissement], [CODE_ETABLISSEMENT_MERE], 0
  from MaTable
 where [CODE_ETABLISSEMENT_MERE] is null
 union all
select t.[CODE_Etablissement], t.[LIB_Etablissement], t.[CODE_ETABLISSEMENT_MERE], c.[LEVEL] + 1
  from MaTable as t
  join CTEr    as c
    on c.[CODE_Etablissement] = t.[CODE_ETABLISSEMENT_MERE]
)
select [CODE_Etablissement], [LIB_Etablissement], [CODE_ETABLISSEMENT_MERE], [LEVEL]
  from CTEr;
 
CODE_Etablissement LIB_Etablissement   CODE_ETABLISSEMENT_MERE LEVEL
------------------ ------------------- ----------------------- -----------
5                  DIRECTION GÉNÉRALE  NULL                    0
4                  DIRECTION RH        5                       1
3                  DIRECTION MARKETING 5                       1
2                  DIRECTION SERVICE   4                       2
1                  DIRECTION SERVICE2  4                       2