Flatten recursive hierarchy

oraclerecursive

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.

Best Answer

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.

Related Question