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
 Loading Hierarchical data

Author  Topic 

Naveensrcl
Starting Member

8 Posts

Posted - 2013-08-03 : 06:03:54
Hi,

CREATE TABLE #Source
(
Id int identity(1,1)
,category int
,Leaf_Node_code varchar(10) --
,Level1_Name varchar(20)
,Level2_Name varchar(20)
,Level3_Name varchar(20)
,Level4_Name varchar(20)
,Level5_Name varchar(20)
)

INSERT INTO #Source
SELECT 1,'101','World','Asia','India',null , null UNION ALL
SELECT 2,'102','a','aa','aaa','aaaa' , null UNION ALL
SELECT 3,'103','b','bb','bbb','bbbb','bbbbb'

Here category 1 has 3 levels ,
category 2 has 4 levels ,
category 3 has 5 levels ,

below is the target table, here Leaf_Node_code should populate to only for leaf nodes for each category ..
Need to populate Node_id with hierarchical data

I am unable frame a sql query to handle different levels , in future #Source may have more levels .
Please guide me on handle multiple hierarchy levels .. here only leaf node should have Leaf_Node_code

CREATE TABLE TARGET_TABLE
(
ID INT IDENTITY(1,1) primary key
,Node_id HIERARCHYID
,category int
,Parent_id int references TARGET_TABLE(id)
,Leaf_Node_code varchar(10)
,Name varchar(20)
)

Here is the expected output:

ID category Parent_id Leaf_Node_code Name Node_id
1 1 NULL NULL World
2 1 1 NULL Asia
3 1 2 101 India
4 2 NULL NULL a
5 2 4 NULL aa
6 2 5 NULL aaa
7 2 6 102 aaaa
8 3 NULL NULL b
9 3 8 NULL bb
10 3 9 NULL bbb
11 3 10 NULL bbbb
12 3 11 103 bbbb

Thanks,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-03 : 06:44:41
have a look at recursive CTEs. Thats what you need to use here.

http://msdn.microsoft.com/en-us/library/ms186243(v=sql.105).aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -