Count child of every node in hier table reading source just once

Hi guys,
I'm trying to write a SQL statement to count how many child every node in a hierarchical table has.
I want to perform this operation reading source table only one time.

Following an example of source table:

CREATE TABLE TEST_HIER
(

ID NUMBER,
PID NUMBER
);

Insert into TEST_HIER
(ID, PID)

Values
(1, 0);

Insert into TEST_HIER
(ID, PID)

Values
(2, 1);

Insert into TEST_HIER
(ID, PID)

Values
(3, 1);

Insert into TEST_HIER
(ID, PID)

Values
(4, 2);

Insert into TEST_HIER
(ID, PID)

Values
(5, 3);

COMMIT; And expected result is:

PID CHILD
---------- ----------
0 1
1 2
2 1
3 1

Any idea?
Thanks a lot
Mauro Received on Tue Mar 18 2008 - 11:37:31 CDT