t-sql help with recursive sort of query
- by stackoverflowuser
Hi
Based on the following table
ID Path
---------------------------------------
1 \\Root
2 \\Root\Node0
3 \\Root\Node0\Node1
4 \\Root\Node0\Node2
5 \\Root\Node3
6 \\Root\Node3\Node4
7 \\Root\Node5
...
N \\Root\Node5\Node6\Node7\Node8\Node9\Node10
so on...
There are around 1000 rows in this table. I want to display individual node in seperate columns. Maximum columns to be displayed 5 (i.e. node till 5 level deep). So the output
will look as below
ID Path Level 0 Level 1 Level 2 Level 3 Level 4 Level 5
----------------------------------------------------------------------------------------
1 \\Root Root Null Null Null Null Null
2 \\Root\Node0 Root Node 0 Null Null Null Null
3 \\Root\Node0\Node1 Root Node 0 Node 1 Null Null Null
4 \\Root\Node0\Node2 Root Node 0 Node 2 Null Null Null
5 \\Root\Node3 Root Node 3 Null Null Null Null
6 \\Root\Node3\Node4 Root Node 3 Node 4 Null Null Null
7 \\Root\Node5 Root Node 5 Null Null Null Null
...
N (see in above table) Root Node 5 Node 6 Node 7 Node 8 Node 9
The only way i can think of is to open a cursor, loop through each row and perform string split, just fetch
the first 5 nodes and then insert into a temp table.
Pls. suggest.
Thanks
