1

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.

APC
  • 144,005
  • 19
  • 170
  • 281
timcase
  • 455
  • 2
  • 4
  • 14
  • possible duplicate of [Creating a flattened table/view of a hierarchically-defined set of data](http://stackoverflow.com/questions/3391809/creating-a-flattened-table-view-of-a-hierarchically-defined-set-of-data) – APC Oct 29 '13 at 04:06
  • You're describing a technique called Transitive Closure. Read my answer to the question linked above for some insight and a useful resource. – APC Oct 29 '13 at 04:07

0 Answers0