table 2 data

[abc2575]

how to transfer given below table 1 data( contains tree structure data) to table 2 specified format: give query or statement or procedure with logic:

table 1

id name level no parent id
1 abc 0 0
2 def 1 0
3 ghi 2 2
4 jkl 2 2
5 mno 3 4
6 pqr 1 0
7 st 2 6
8 uv 3 7
9 wx 4 8
10 yz 3 7
11 ac 1 0
12 hk 2 11
.
.
.
.

I require table 1 data in a format given below :

table 2 format

level0 level1 level2 level3 level4
abc na na na na
abc def na na na
abc def ghi na na
abc def jki na na
abc def jki mno na
abc pqr na na na
abc pqr st na na
abc pqr st uv na
abc pqr st uv wx
abc pqr st yz na
abc ac na na na
abc ac hk na na
.
.
.
.
na= not available

please give complete sql statment or procedure through which I get table 2 data.

 

[DougP]

When ever you need to loop through records and then re-write them back out in a completely different way there is only one way to do it.
Create a function in VBA that does just that.
Opens the table loops through each record
Or in your case gets all records from a certain "level" then writes them out in the new format.
Moves to the next level writes those out.

Sometimes there is considerable trial and error that has to be done too.


DougP, MCP
dposton@universal1.com

Ask me how Bar-codes can help you be more productive.