Hiearchy Forming - 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

Hiearchy Forming

Author

Topic

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2013-05-31 : 19:01:46

Hi,

I need the help on forming parent/child hiearchy in T-sql statement,
I will be giving input as


Declare @parentid int
set @parentid=50


DECLARE @Categories TABLE (
        ParentID INT, 
        ParentName VARCHAR(20),
         ChildID INT )


        INSERT INTO @Categories(ParentID, ParentName, ChildID)
			
            SELECT 50,'Dept A', 501 UNION ALL
            SELECT 50,'Dept B', 502 UNION ALL
            SELECT 50,'Dept C', 503 UNION ALL
            SELECT 50,'Dept D', 504 UNION ALL
            SELECT 50,'Dept E', 505 UNION ALL
            SELECT 50,'Dept F', 506 UNION ALL
            select 501 ,'Dept G',810 UNION ALL
            select 501 ,'Dept H',820 UNION ALL
            select 810,'Dept K',1000 
            select 810 ,'Dept I',2000 UNION ALL

so all the child under parent (ie) 50 should get displayed.
output should be:


ParentID	ParentName	ChildID
50	Dept A	501
50	Dept B	502
50	Dept C	503
50	Dept D	504
50	Dept E	505
50	Dept F	506
501	Dept G	810
501	Dept H	820
810	Dept K	1000
810	Dept I	2000

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-05-31 : 23:16:08

if you are using sql 2005 or later you can use a recursive common table expression to do this:


;with hierachy as
(
       select ParentID, ParentName, ChildID, 1 lev
       from   @categories
       where  parentid = @parentid
       
       union all
       
       select c.parentid, c.parentName, c.childid, h.lev + 1
       from   hierachy h
       join   @categories c on c.parentid = h.childid
       
)
select * from hierachy

OUTPUT:
ParentID    ParentName           ChildID     lev
----------- -------------------- ----------- -----------
50          Dept A               501         1
50          Dept B               502         1
50          Dept C               503         1
50          Dept D               504         1
50          Dept E               505         1
50          Dept F               506         1
501         Dept G               810         2
501         Dept H               820         2
810         Dept K               1000        3
810         Dept I               2000        3

(10 row(s) affected)

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources