Your solution worked fine! Thanks for that.
Unfortunately, I got extra requirements: a new column "Value2" was added.
I tried to edit your query like this:
But I'm not familiar with "1.E", so I don't know well what to change there...
I thought it would refer to the 5th column of table Tbl, which is value, but when I tried "1.F" for Value2, I got a lot of errors...
DROP TABLE Tbl;
CREATE TABLE Tbl(
ID INTEGER NOT NULL PRIMARY KEY
,Parent INTEGER
,Type VARCHAR(23) NOT NULL
,Level INTEGER NOT NULL
,Value INTEGER NOT NULL
,Value2 INTEGER NOT NULL
);
INSERT INTO Tbl(ID,Parent,Type,Level,Value,Value2) VALUES (1,NULL,'Main',1,0,10);
INSERT INTO Tbl(ID,Parent,Type,Level,Value,Value2) VALUES (2,1,'Sub1',2,0,25);
INSERT INTO Tbl(ID,Parent,Type,Level,Value,Value2) VALUES (3,2,'Detail1',3,10,0);
INSERT INTO Tbl(ID,Parent,Type,Level,Value,Value2) VALUES (4,3,'DetailOfDetail1',4,5,0);
INSERT INTO Tbl(ID,Parent,Type,Level,Value,Value2) VALUES (5,3,'DetailOfDetail2',4,10,0);
INSERT INTO Tbl(ID,Parent,Type,Level,Value,Value2) VALUES (6,5,'DetailOfDetailOdDetail1',5,5,0);
INSERT INTO Tbl(ID,Parent,Type,Level,Value,Value2) VALUES (7,2,'Detail2',3,20,25);
INSERT INTO Tbl(ID,Parent,Type,Level,Value,Value2) VALUES (8,2,'Detail3',3,50,0);
INSERT INTO Tbl(ID,Parent,Type,Level,Value,Value2) VALUES (9,1,'Sub2',2,0,50);
INSERT INTO Tbl(ID,Parent,Type,Level,Value,Value2) VALUES (10,9,'Detail4',3,20,10);
INSERT INTO Tbl(ID,Parent,Type,Level,Value,Value2) VALUES (11,10,'DetailOfDetail3',4,5,0);
INSERT INTO Tbl(ID,Parent,Type,Level,Value,Value2) VALUES (12,10,'DetailOfDetail4',4,10,0);
INSERT INTO Tbl(ID,Parent,Type,Level,Value,Value2) VALUES (13,9,'Detail5',3,10,5);
INSERT INTO Tbl(ID,Parent,Type,Level,Value,Value2) VALUES (14,9,'Detail6',3,5,0);
INSERT INTO Tbl(ID,Parent,Type,Level,Value,Value2) VALUES (15,14,'DetailOfDetail5',4,5,0);
INSERT INTO Tbl(ID,Parent,Type,Level,Value,Value2) VALUES (16,15,'DetailOfDetailOdDetail2',5,15,0);
INSERT INTO Tbl(ID,Parent,Type,Level,Value,Value2) VALUES (17,1,'Sub3',2,0,40);
INSERT INTO Tbl(ID,Parent,Type,Level,Value,Value2) VALUES (18,17,'Detail7',3,5,0);
INSERT INTO Tbl(ID,Parent,Type,Level,Value,Value2) VALUES (19,18,'DetailOfDetail5',4,20,10);
INSERT INTO Tbl(ID,Parent,Type,Level,Value,Value2) VALUES (20,19,'DetailOfDetailOdDetail2',5,10,0);
INSERT INTO Tbl(ID,Parent,Type,Level,Value,Value2) VALUES (21,17,'Detail8',3,25,10);