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 #SourceSELECT 1,'101','World','Asia','India',null , null UNION ALLSELECT 2,'102','a','aa','aaa','aaaa' , null UNION ALLSELECT 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 World2 1 1 NULL Asia3 1 2 101 India4 2 NULL NULL a5 2 4 NULL aa6 2 5 NULL aaa7 2 6 102 aaaa8 3 NULL NULL b9 3 8 NULL bb10 3 9 NULL bbb11 3 10 NULL bbbb12 3 11 103 bbbbThanks, |
|