how to nest hierarchical rows - 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

SQL Server 2005 Forums

Transact-SQL (2005)

how to nest hierarchical rows

Author

Topic

johnsql
Posting Yak Master

161 Posts

Posted - 2009-04-05 : 09:49:27

Hi,
I have an original table "MyTable" with nodes like followings


        MyTable's data

NodeID   ParentNodeID            Name    Level
-------  -------------          ------   -----
1          null                  Root       0
2          1                     A          1
3          1                     B          1
4          2                     C          2
5          2                     D          2
6          3                     E          2
7          3                     F          2
8          4                     G          3 
9          4                     H          3
10         4                     I          3
11         5                     J          3

I would like to generate a temporary table #tempTable from MyTable with nodes respected to their node parent and ancestors as following:


#tempTable

NodeID   ParentNodeID            Name    Level
-------  -------------          ------   -----
1          null                  Root       0
2          1                     A          1
4          2                     C          2
8          4                     G          3 
9          4                     H          3
10         4                     I          3
5          2                     D          2
11         5                     J          3
3          1                     B          1
6          3                     E          2
7          3                     F          2

Thanks very much in advance.

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-04-06 : 00:12:56

[code]
declare @temp table (NodeID INT, ParentNodeID INT, Name varchar(32), Level int)
insert into @temp select 1 , null , 'Root' , 0
insert into @temp select 2 , 1 , 'A' , 1
insert into @temp select 3 , 1 , 'B' , 1
insert into @temp select 4 , 2 , 'C' , 2
insert into @temp select 5 , 2 , 'D' , 2
insert into @temp select 6 , 3 , 'E' , 2
insert into @temp select 7 , 3 , 'F' , 2
insert into @temp select 8 , 4 , 'G' , 3
insert into @temp select 9 , 4 , 'H' , 3
insert into @temp select 10 , 4 , 'I' , 3
insert into @temp select 11 , 5 , 'J' , 3

;with cte(nodeid,parentnodeid,name,path,level) as
(
select nodeid,parentnodeid,name,cast(nodeid as varchar(max)),0 from @temp
where parentnodeid is null

union all

select t.nodeid,t.parentnodeid,t.name,c.path+'-'+cast(t.nodeid as varchar(max)),c.level+1 from @temp t
inner join cte c on c.nodeid = t.parentnodeid
)
select nodeid,parentnodeid,name,level from cte order by path

[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources