部署の情報を格納したテーブルを作っています。

ここには、ParentIdという列に、1つ上の親の部署Idを格納しています。ここから、上下の関係を導出する...という具合に考えているのですが、この方法だと、どうしても再帰クエリになってしまいます。

Oracleでは、CONNECTというのを使うとうまくできそうなんですが、SQL Serverだとどうしていいのか分かりません...。

（階層は深くても4階層くらいまでなので、固定のSQLでも頑張ればいいのですが）

また、データはDBはRDBにフラットに定義しておいて、実際にアプリケーションに渡す場合は、XMLに加工して使おうと思っています。

色々探していたら、CTEというのが使えるんですね。

正しい方法じゃないかもしれませんが、とりあえず、やってみると....。

USE Common

GO
WITH Recursive (ParentId, Id, Code, Name, Level)
AS
(
-- Divisionの定義
    SELECT D.ParentId, D.Id, D.Code, D.Name, 
        0 AS Level
    FROM Division AS D
    WHERE ParentId IS NULL
	AND IsEnabled = 1
    UNION ALL
-- 再帰の定義
    SELECT D.ParentId, D.Id, D.Code, D.Name, 
        Level + 1
    FROM Division AS D
    INNER JOIN Recursive AS R
        ON D.ParentId = R.Id
	AND IsEnabled = 1
)
-- 再帰テーブルから取り出し
SELECT ParentId, Id, Code, Name, Level, replicate('-', (Level)* 10) + Code 
FROM Recursive