MS-SQLで階層を表現する

with c as (
select '/'+cast(billID as varchar(49)) as path,BillID
from dbo.bill b
where b.parentBillID is null
union all
select cast(c.path+'/'+CAST(b.billID as varchar(4)) as varchar(50)), b.billID
from dbo.bill b join c on b.parentBillID = c.billID)
select c.path+'/',b.descr
from c join dbo.bill b on c.billID = b.billID
order by 1;

MS-SQLの階層にRowNumみたいな順番が無いので、一番最初の項目にlevelを表現している。