开发者

query an hierarchy, tree, table with an FK on itself

开发者 https://www.devze.com 2023-01-26 10:14 出处:网络
I have this table Id PIdNameValue ------------------------ 1nullfoonull 21barnull 32foobarhi 41bar1开发者_StackOverflow社区hey

I have this table

Id PId      Name   Value
------------------------
1  null     foo     null
2  1        bar     null
3  2        foobar  hi
4  1        bar1    开发者_StackOverflow社区hey

I need a query that would give me this result:

Name           | Value
-----------------------
foo bar foobar | hi
foo bar1       | hey


with s( id, pid, name, value) as
(
select f.id, f.pid, cast(f.name as nvarchar) as name, f.value from foos f 
where f.pid is null
union all
select f.id, f.pid, cast(s.name + f.name as nvarchar) as name, f.value from foos f inner join s on f.pid = s.id
)
select * from s
where not exists(select * from foos where pid = s.id)
0

精彩评论

暂无评论...
验证码 换一张
取 消