Select Help? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select Help?

Author

Topic

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2007-02-17 : 12:09:39

Hi,

ID NAME PARENTID
1 ABC
2 Sales 1
3 Avdertisement 2
4 Accounting 1
5 DEF
...

Select name from [mytable] where ID = 1

if ID = 1 than NAME is ABC
if ID = 2 than NAME is ABC/Sales
if ID = 3 than NAME is ABC/Sales/Advertisement
if ID = 4 than NAME is ABC/Accounting
and
if ID = 5 than NAME is DEF

How can i do a generic SELECT statement to do the sample above?
But above is just a sample, there may be 5 sub departments, may be 100.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-02-17 : 12:48:27

in sql server 2005 you can use CTE's to handle this kind of recursion.
read up on them

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-18 : 11:15:09

Try this (SQL 2005 only):

Declare @ID int, @Dept varchar(max)
Set @ID = 3;

With DeptHier(ID, ParentID)
as
(
	Select ID, ParentID
	From Dept
	Where ID = @ID

	union all

	Select Dept.ID, Dept.ParentID
	From Dept JOIN DeptHier
	On Dept.ID = DeptHier.ParentID
)
Select @dept = Coalesce(@dept + '/', '') + d.Name 
from Dept d Join DeptHier dh 
on d.ID = dh.ID
order by dh.ParentID

select @dept as Dept

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources