1

Consider the table data below:

emp_name VARCHAR(25), path VARCHAR(150)
Albert                /Albert
John                  /Albert/John
Chuck                 /Albert/Chuck
Tom                   /Albert/John/Tom
Frank                 /Frank

I want to get a list of superiors of Tom:

John
Albert

(can include Tom)

Is it possible to do with without splitting the path and then using a sequence table (only way I found)?

DB is Sql server 2008 R2

Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281
user666423
  • 229
  • 3
  • 14

1 Answers1

1

You can use a recursive CTE to split the hierarchy string value.

declare @T table 
(
  ID int identity,
  emp_name varchar(25),
  [path] varchar(150)
)

insert into @T values
('Albert', 'Albert'),
('John',   'Albert/John'),
('Chuck',  'Albert/Chuck'),
('Tom',    'Albert/John/Tom'),
('Frank',  'Frank')

declare @EmpName varchar(25) = 'Tom'

;with cte(Sort, P1, P2, [path]) as
(
  select 1,
         1,
         charindex('/', [path]+'/', 1),
         [path]
  from @T
  where emp_name = @EmpName  
  union all
  select Sort+1,
         P2+1,
         charindex('/', [path]+'/', C.P2+1),
         [path]
  from cte as C
  where charindex('/', [path]+'/', C.P2+1) > 0
)
select substring([path], P1, P2-P1)
from cte
order by Sort

Result:

(No column name)
Albert
John
Tom

Test the query here: https://data.stackexchange.com/stackoverflow/q/101383/

Another thing you can try

select T2.emp_name
from @T as T1
  inner join @T as T2
    on '/'+T1.[path]+'/' like '%/'+T2.emp_name+'/%' and
       T2.emp_name <> @EmpName  
where T1.emp_name = @EmpName

https://data.stackexchange.com/stackoverflow/q/101518/get-hierarchy-with-join-using-like

Community
  • 1
  • 1
Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281
  • Is it possible to do this without using a recursive method? – user666423 May 26 '11 at 17:12
  • @user - Sure. As you said using a sequence table. Why don't you want to use a recursive CTE? Does it have performance issues for you? You could also use a string-split function. There are a bundle of them published here on SO. – Mikael Eriksson May 26 '11 at 17:21
  • Where on SO (link)? I tried to do it using a sequence table only but couldn't figure it out. – user666423 May 26 '11 at 18:19
  • @user - http://stackoverflow.com/questions/314824/t-sql-opposite-to-string-concatenation-how-to-split-string-into-multiple-recor – Mikael Eriksson May 26 '11 at 19:19