[SQL Server] ツリー構造 再起クエリで子から祖先を取得

※備忘録用

サイトのトップページに表示させるコンテンツのカテゴリー情報をツリー構造で保持している。パンくずを表示させるために、子から祖先の情報を取得したい。

DB

DB Categories table

Id	parentId	CategoryName
1	null	カテゴリー1
2	1	カテゴリー2
3	2	カテゴリー3
4	3	カテゴリー4

SQL

カテゴリー4から祖先の情報をすべて取得する場合。
※SQL Server

with  ancestor  (layer, Id, ParentId, CategoryName) as (
    select 0,Id, ParentId, CategoryName from Categories where Categories.CategoryCode = 4
    union all
        select 
            ancestor.layer + 1,
            Categories.Id,
            Categories.ParentId,
            Categories.CategoryName
        from ancestor, Categories
        where ancestor.parent = Categories.Id)

select layer, Id, ParentId, CategoryName from ancestor order by layer;