>Hi,
>
>If I have the following records in a table.
>SID Desc PID LX
>1....A....0....0
>3....B....1....1
>5....C....3....2
>7....D....5....3
>8....E....14...3
>14...F....3....2
>21...G....3....2
>22...H....21...3
>
>and I want to sort so the table looks like a tree structure
>
>SID Desc PID LX
>1....A....0....0
>3....B....1....1
>5....C....3....2
>7....D....5....3
>14...F....3....2
>8....E....14...3
>21...G....3....2
>22...H....21...3
>
>SID - Structure ID
>PID - Parent ID
>LX - Level in the tree
>
>Could this be done using a select statement? Any help or suggestion is appreciated.
>
My serious doubts to any attempt to do such a recursive thing as tree structure in a single SQL statement. The way I always do to traverse a tree (and sort it into the same order it would be displayed in a treeview) is to do a recursive select:
[this will be meta-code]
select parent records from originaltable
for each parent
insert from current record into finaltable
checkchildren(parentkey)
endfor
proc checkchildren (pKey)
select * from originaltable where parent=pkey ;
into cursor [name depends on level]
scan
insert into finaltable
checkchildren(parentkey)
endscan
select [previous cursor here]
return
What I usually do is to create a treeview key, usually in the form of ACAB, where each character corresponds to the ordinal value of the row at a level, so this one would be #1 on level1, #3 on level2, #1 on level3 and #2 on level4. This allows for easier indexing and/or sorting of the final table.
Sorry if this sound a little confused, but that's the only sure way I've found so far, and I'm doing these things for 11 years.