大家好，都回去过年了吗？请问转换成树状层级结构显示问题

[一缕青烟]

数据库里存放了以下数据
a\b\1.txt
a\b\2.txt
a\2.jpg
b\1\5.jpg

需要的查询结果显示为：
a
b
1.txt
2.txt
2.jpg
b
1
5.jpg

谢谢

[骑着蜗牛去爬山]

with tb(txt)
as(
select 'a\b\1.txt' union all
select 'a\b\2.txt' union all
select 'a\2.jpg' union all
select 'b\1\5.jpg')
,cte
as(
select 
[all]=substring(t.txt+'\',1,number-1),
txt=(case when charindex('\',reverse(substring(t.txt+'\',1,number-1)))-1<0 then substring(t.txt+'\',1,number-1) else
right(substring(t.txt+'\',1,number-1),
charindex('\',reverse(substring(t.txt+'\',1,number-1)))-1)
end)
 from tb t,master..spt_values where type='p' and number between 1 and len(t.txt)+1 and substring(t.txt+'\',number,1)='\'
)
select distinct txt,[all] from cte order by [all]

[haitao]

程序做这个比较合适