CODEWITHSUNDEEP
sql-serversql-server-2008
whoisearth
whoisearth

Reputation: 4170

sql - display grandparent, parent and child

I'm trying to create a view which will return a string of 3 levels of data.

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

The above is only returning the top level grandparent and the parent but not the child level.

This is what the table looks like

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

Right now my query will return Portfolio Strategy Functions \ Research \ Economic Forecasting but I want it to also return Portfolio Strategy Functions \ Research which it doesn't do.

Upvotes: 0

Views: 441

Answers (1)

DimaSUN
DimaSUN

Reputation: 921

I tried to solve

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

And this is result

(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)

Upvotes: 2

Related Questions