Hierarchy store procedure -- upline

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

SQL Server 2008 Forums

Transact-SQL (2008)

Hierarchy store procedure -- upline

Author

Topic

nony.inbox
Starting Member

2 Posts

Posted - 2010-07-21 : 13:18:50

I have 2 tables
1. document_Folder
-intDirectoryID int
-strName

data
-------
1, doc
2, manuals
3, images
4, etc

2. DocumentFolderStructure
-intChildNode int
-intParentNode int

data
-------
1, null
2,1
3,1
4,2

i want a query to get the hirarchi of all directories
i.e
if i select 3 it give me
1/3

but if i select 4
it give me 1/2/4

thanks in advance

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-07-21 : 15:37:30

try this:


create table DH
(child int
,parent int
)

insert into DH
select 1,null union all
select 2,1 union all
select 3,1 union all
select 4,2
--(4 row(s) affected)



select 
p.child
,p.parent
,c.parent
,cast(p.child as varchar(5))+ '/' 
+ cast(p.parent as varchar(5)) + '/' 
+ isnull(cast(c.parent as varchar(5)),'') as pathDH

 from DH as p
	join DH as c
	on p.parent = c.child

nony.inbox
Starting Member

2 Posts

Posted - 2010-07-21 : 15:45:58

here is the problem
1. I dont know the depth its unknown
2. incase of root its not showing any thing

I appriciate your answer

PavanKK
Starting Member

32 Posts

Posted - 2010-08-02 : 05:53:57

try following.

---------

DECLARE @document_Folder TABLE(intDirectoryID int,strName VARCHAR(50))
DECLARE @DocumentFolderStructure TABLE (intChildNode int,intParentNode int)
DECLARE @DID INT = 4

INSERT INTO @document_Folder VALUES
(1,'doc'),
(2,'manuals'),
(3,'images'),
(4,'etc')

INSERT INTO @DocumentFolderStructure VALUES
(1,null),
(2,1),
(3,1),
(4,2)

;WITH Cte AS
(
SELECT @DID intParentNode, 1 Level

UNION ALL

SELECT T.intParentNode,C.Level + 1
FROM Cte C
JOIN @DocumentFolderStructure T ON C.intParentNode = T.intChildNode

)

SELECT STUFF((SELECT TOP 100 PERCENT '/'+CONVERT(VARCHAR,intParentNode) FROM Cte
WHERE intParentNode IS NOT NULL
ORDER BY Level DESC FOR XML PATH('')),1,1,'')

-------------------

KK :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources