組織の部署名を、再帰クエリで表示するには

はじめは、何が何だかワケワカランだったけど、やってることが分かったから、忘れないうちに残しとこ。

データベースのテーブルに、部署データを入れて、いろいろと使いたいというとき、どんなテーブルを創造するだろう？

なので、十人いれば九人が、こんな感じになると思います。

データベースのテーブルから、欲しい情報を得るためにＳＱＬ文を書きます。

今の話題で欲しい情報は、長い部署名となりますが、ちょっと汎用的な項目を追加しています。

この部署マスター情報（M_Department）というテーブルから、次のような形で情報をえたい。

それが、このようなＳＱＬ文になった。

with TemporaryTable (

 Depth
,UID
,Code
,Name
,ParentUID
,FullName

) as (

 select
 1 AS Depth
,UID
,Code
,Name as Base_Name
,ParentUID
,case
    when HideFlag = 0 then cast(Name as nvarchar(max))
    when HideFlag = 1 then ''
    else ''
 end as FullName
 from
 M_Department
 where
 ParentUID is NULL

 union all

 select
 Parent.DEPTH + 1
,Child.UID
,Child.Code
,Child.Name 
,Child.ParentUID
,case
    when HideFlag = 0 then Parent.FullName + Child.Name
    when HideFlag = 1 then ''
    else ''
 end
 from
 TemporaryTable Parent
,M_Department Child
 where
 Parent.UID = Child.ParentUID

)

 select * from TemporaryTable where UID = 4 order by Code

ここからは、このＳＱＬ文を見ただけで理解できる天才には、用がない解説です。

with TemporaryTable (

 Depth
,UID
,Code
,Name
,ParentUID
,FullName

) as (

まずは、「TemporaryTable」と、勝手に名付けた一時テーブルの要素に欲しい項目の定義をしています。

) as (

 select
 1 AS Depth
,UID
,Code
,Name as Base_Name
,ParentUID
,case
    when HideFlag = 0 then cast(Name as nvarchar(max))
    when HideFlag = 1 then ''
    else ''
 end as FullName
 from
 M_Department
 where
 ParentUID is NULL

 union all

続く、select文では、親部署コードがない（Null値）のレコードを、元となるテーブル「部署マスター情報（M_Department）」から呼び出しています。

親部署コードがないというのは、最上位部署の位置づけってことになります。これ以上、上がないから、親部署コードがNull値になっているという訳です。

さて、コード中で異色なのは、case文の辺りでしょうか。

,case
    when HideFlag = 0 then cast(Name as nvarchar(max))
    when HideFlag = 1 then ''
    else ''
 end as FullName

この世の中には「部署名でも、特に表記しなくていい部署があるだろう」ということで、HideFlagという非表示区分を設けましたので、非表示にしたい場合は、部署名を出さない（blankにする）という条件分岐の処理をしています。抽出条件で、非表示区分を指定しないのは、そこで、部署と親部署の関係が切れてしまうことを避けるため。

その条件分岐の中に、cast文で、あえて、nvarchar(max)と型を宣言している一文があります。これは、数多あるデータベースの仕様によって、要不要が変わるようです。後の処理で、それぞれのレコードにある部署名（Name）を、連結していく訳ですが、一部の阿呆な子は、連結していく項目の型が判らなくなってしまう様で「できない！」とエラー泣きするのです。そのため、「この項目はnvarchar(max)という型ですよ」というcastをして、データベースに教えているのです。

 union all

 select
 Parent.DEPTH + 1
,Child.UID
,Child.Code
,Child.Name 
,Child.ParentUID
,case
    when HideFlag = 0 then Parent.FullName + Child.Name
    when HideFlag = 1 then ''
    else ''
 end
 from
 TemporaryTable Parent
,M_Department Child
 where
 Parent.UID = Child.ParentUID

)

union allは、二つの結果を連続させるような意味合いなので、次のＳＱＬ文では、一時テーブル（TemporaryTable）と、部署マスター情報（M_Department）を、一時テーブルの部署コードと部署マスター情報の親部署コードを紐づけるという条件で、読み取ろうとしています。この条件のおかげで、先に読んで、一時テーブルに格納されている親部署コード＝nullの条件に当てはまる部署が省かれています。

この中で、重要なのは、次の二つ。

• 深度が一時テーブル側の深度に＋１になっていること。
• 連結部署名が、一時テーブルの連結部署名の後ろに、部署マスター情報の部署名を、追加していること。

ここまでが、一時テーブル（TemporaryTable）をつくるＳＱＬ文になります。一時テーブルをつくる過程で、一時テーブルを読み込むので、「再帰」っていうそうです。

さあ！最後は一時テーブルを読むだけ。

 select * from TemporaryTable where UID = 4 order by Code

最後のselect文で、実際に欲しい一時テーブルの項目を指定して読み込んでもいいし、抽出条件を指定して、目的の部署だけに絞って読み込んでもいいし、抽出結果を好きな項目で並べ替えてもいいし、と、お気に召すままに読み込めます。