再帰クエリって何さ！？

テーブル設計

　組織内の部署情報をデータベース化したい場合、そのテーブル構造をどの様に設計しましょう？
　前提として、各部署の情報には「部署コード」と「部署名」が在るとし、各部署は、階層構造で組織化されています。
　例）営業部 ＞ 営業一課 ＞ 営業一係

テーブルを定義

　テーブル設計のパターン２に則り、テーブルを定義します。
　テーブル名は「部署マスター」として、項目の定義の際に、汎用的な項目を追加してみました。

部署コード	親部署コード	任意コード（※１）	部署名	非表示区分（※２）
UID	ParentUID	Code	Name	HideFlag
1	Null	1	会社	True
2	1	11	営業部	False
3	2	111	営業一課	False
4	3	1111	営業一係	False
5	3	1112	営業二係	False
6	2	112	営業二課	False

（※１）「任意コード」は、データベース内の部署コードとは異なり、利用者が任意に付けられる人間側の「部署コード」として追加しました。
（※２）「非表示区分」は、組織名を表に出す必要のない組織の名称を連結表示させないための区分として追加しました。

テーブルから抽出したい情報

　定義したテーブルから、次のような形で情報を抽出したいと思います。

階層深度	部署コード	任意コード	部署名	親部署コード	連結名
Depth	UID	Code	Name	ParentUID	FullName
1	1	1	会社	Null
2	2	11	営業部	1	営業部
3	3	111	営業一課	2	営業部営業一課
4	4	1111	営業一係	3	営業部営業一課営業一係
4	5	1112	営業二係	3	営業部営業一課営業二係
3	6	112	営業二課	2	営業部営業二課

さっそくSQL文（全文）

　欲しい情報を抽出する再帰クエリのSQL文はこちらです。
　任意コードが１１１１の「営業一係」の情報を抽出しています。

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 Code = '1111' order by Code

解説

　ここからは、このSQL文を見ただけで理解できる方には用がない解説となっています。

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値）のレコードを、元となるテーブルから抽出しています。
　親部署コードがないというのは、組織の最上位ってことになります。上がないから親部署コードをNull値（空っぽ）にしています。

　さて、このコード中で異色なのは、case文の辺りですが、これは非表示区分による条件分岐で、組織名を表示する場合と表示しない場合に分けています。

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

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

　union allは、二つの結果を連結させます。

 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

)

　このSQL文では、部署マスターの親部署コードと一時テーブルの部署コードを紐づけして、部署名を連結しながら抽出しています。親が主になるので、親に帰属する子のレコード（一時テーブル（親）の部署コードを親部署コードにもつ部署マスター（子）のレコード）を抽出するようになっています。帰属する子が無くなるまで再帰してます。
　ここまでが一時テーブルの情報を作成しています。

　さあ！最後は一時テーブルから欲しい情報を抽出するだけです。

 select * from TemporaryTable where Code = '1111' order by Code

　最後のselect文では、実際に欲しい一時テーブルの必要な項目名だけ抽出しても良いですし、抽出条件を指定して、目的の部署の情報だけに絞って抽出しても良いです。

　「再帰」とは「自分で自分を呼び出す」ということです。
　永遠に自分で自分を呼び続けたら無限ループ。「再起不能」って「再帰不能」ってことですね。