t-sql help with recursive sort of query

Posted by stackoverflowuser on Stack Overflow See other posts from Stack Overflow or by stackoverflowuser
Published on 2010-06-16T20:58:18Z Indexed on 2010/06/16 21:02 UTC
Read the original article Hit count: 204

Filed under:
|
|

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

© Stack Overflow or respective owner

Related posts about sql

Related posts about tsql