SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Select Help?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 02/17/2007 :  12:09:39  Show Profile  Click to see raysefo's MSN Messenger address  Reply with Quote
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

Slovenia
11750 Posts

Posted - 02/17/2007 :  12:48:27  Show Profile  Visit spirit1's Homepage  Reply with Quote
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
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5509 Posts

Posted - 02/18/2007 :  11:15:09  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
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"

Edited by - harsh_athalye on 02/18/2007 11:16:12
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000