Flatten recursive hierarchy I have the following recursive query:

SELECT SYS_CONNECT_BY_PATH(object_name, '\') AS path
FROM user_group_nodes
START WITH parent_id = '2892107544AA2278166C1AA8D123E761'
CONNECT BY parent_id = PRIOR object_id
ORDER SIBLINGS BY object_name;


Which returns the following sample dataset:

\UserGroupA\UserGroupAA\UserGroupAAA\JaneDoe
\UserGroupA\UserGroupAA\UserGroupAAA\UserGroupAAAA\UserGroupAAAAA\JohnDoe


Based on the above sample dataset, I need to insert the following records into a table:

Group            User
---------------  ---------------
UserGroupA       JaneDoe
UserGroupAA      JaneDoe
UserGroupAAA     JaneDoe
UserGroupA       JohnDoe
UserGroupAA      JohnDoe
UserGroupAAA     JohnDoe
UserGroupAAAA    JohnDoe
UserGroupAAAAA   JohnDoe


Can someone provide direction on how to accomplish this? I'm leaning towards using a cursor, but I'm hoping there is another option that performs better since I need to do this across 10,000 users and 1,500 groups.
I've knocked up a MODEL clause to do this.

Sample data:

create table sampledata
(
  dat varchar(255)
);

insert into sampledata values ('\UserGroupA\UserGroupAA\UserGroupAAA\JaneDoe');

insert into sampledata values ('\UserGroupA\UserGroupAA\UserGroupAAA\UserGroupAAAA\UserGroupAAAAA\JohnDoe');


Query:

WITH PIVOTEDDATA AS(
SELECT   * -- RN, POSITION, DAT
  FROM   SAMPLEDATA
   MODEL
    RETURN UPDATED ROWS
    PARTITION BY(ROWNUM RN)
    DIMENSION BY (0 POSITION)
    MEASURES     (DAT ,NVL(LENGTH(REGEXP_REPLACE(DAT,'[^\\]+','')),0) DATA, REGEXP_SUBSTR(DAT,'[^\\]+',1,NVL(LENGTH(REGEXP_REPLACE(DAT,'[^\\]+','')),0)) USERNAME )
    RULES 
    (DAT[FOR POSITION FROM  1 TO DATA[0]-1 INCREMENT 1] = 
      REGEXP_SUBSTR(DAT[0],'[^\\]+',1,CV(POSITION)),
    USERNAME[FOR POSITION FROM  1 TO DATA[0]-1 INCREMENT 1] = REGEXP_SUBSTR(DAT[0],'[^\\]+',1,NVL(LENGTH(REGEXP_REPLACE(DAT[0],'[^\\]+','')),0)))
)
SELECT *
FROM PIVOTEDDATA;


There will be an easier way to do this, but I needed some MODEL practice and my brain hurt thinking about how to do the unpivoting and keeping the last record.

SQL Fiddle.

If you've never used MODEL before, my apologies.