Convert Nested Sets to Adjacency List

Hi,
Can anyone give me an example of how to convert a tree structure
stored as a nested set into an Adjacency List?
I.e
Table(Node_ID, Left, Right)
into
Table(Node_ID, Parent_ID)
I can't get my head around the sql to do it!
(im using SQLS 2000)
Thanks
Andrew
Here is one way to convert nested set to adjacency list:
CREATE TABLE NestedSet (
NodeId CHAR(1) NOT NULL PRIMARY KEY,
Lf INT NOT NULL,
Rg INT NOT NULL)
INSERT INTO NestedSet (NodeId, Lf, Rg) VALUES ('A', 1, 9)
INSERT INTO NestedSet (NodeId, Lf, Rg) VALUES ('B', 2, 3)
INSERT INTO NestedSet (NodeId, Lf, Rg) VALUES ('C', 4, 7)
INSERT INTO NestedSet (NodeId, Lf, Rg) VALUES ('D', 5, 6)
CREATE TABLE AdjacencyList (
NodeId Char(1) NOT NULL PRIMARY KEY,
ParentId Char(1) NULL)
INSERT INTO AdjacencyList
SELECT A.NodeId,
B.NodeId As ParentId
FROM NestedSet A
LEFT OUTER JOIN NestedSet B
ON B.Lf = (SELECT MAX(C.Lf)
FROM NestedSet C
WHERE C.Lf < A.Lf
AND C.Rg > A.Rg)
SELECT NodeId, ParentId FROM AdjacencyList
DROP TABLE NestedSet
DROP TABLE AdjacencyList
Regards,
Plamen Ratchev
http://www.SQLStudio.com