sql - 显示祖父母，父母和孩子

Question

我想创建一个视图，它将返回一个3级数据的字符串。

select bf.functionName + ' \ ' + ISNULL(bf1.functionName + ' \ ', '') + ISNULL(bf2.functionName, '') from tblBusinessFunction bf 
inner join tblBusinessFunction bf1 on bf1.parentID = bf.id and bf.level = 0 
inner join tblBusinessFunction bf2 on bf2.parentID = bf1.id and bf1.level = 1 

以上只是返回顶级祖父母和父母，而不是子级别。

这就是表看起来像

| id | functionName    | parentID | level | 
|:-----|----------------------------:|:--------:|:-------:| 
| 101 | Portfolio Strategy Functions| NULL | 0 | 
| 110 | Research     | 101 | 1 | 
| 111 | Economic Forecasting  | 110 | 2 | 

现在我的查询将返回Portfolio Strategy Functions \ Research \ Economic Forecasting，但我希望它也返回Portfolio Strategy Functions \ Research它不会做。

Answer 1

我试图解决

declare @T table (id int, functionName varchar(50), parentid int, level int) 

insert @T 
values 
(101,'Portfolio Strategy Functions',null,0), 
(110,'Research',101,1), 
(111,'Economic Forecasting',110,2) 

;with Outline as 
(select id,level,functionName = convert(varchar(max),functionName) from @T where level = 0 
    union all 
    select T.ID, T.level, functionName = O.functionName +'/'+ T.functionName from @T T 
    join Outline O on O.id = T.parentid 
) 

select * from OutLine 
where level > 0 

这是导致

(3 row(s) affected) 
id   level  functionName 
----------- ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
110   1   Portfolio Strategy Functions/Research 
111   2   Portfolio Strategy Functions/Research/Economic Forecasting 

(2 row(s) affected)