When you go to "Manage > Organizational Views and Groups" and select either a Org View or an Org Group, if one or more Org Groups contain resources, the Organizational Group column will display the sub-group hierarchy as a single string value, by seperating each node by a backslash:
The following query will allow you to target either an Org View or an Org Group so that you can see this same representation by directly interrigating the database:
declare @parentGuid uniqueidentifier; set @parentGuid = N'Guid of Org View or Org Group goes here'; declare @folders TABLE ( ScopeCollectionGuid uniqueidentifier , ParentScopeCollectionGuid uniqueidentifier , BaseGuid uniqueidentifier) declare @OrganizationalGroup TABLE (ScopeCollectionGuid uniqueidentifier , OrganizationalGroup nvarchar(1000)) INSERT @folders SELECT DISTINCT ScopeCollectionGuid = fbf.[FolderGuid] , ParentScopeCollectionGuid = f.[ParentFolderGuid] , BaseGuid = ISNULL(ipf.BaseGuid, fbf.FolderGuid ) FROM FolderBaseFolder fbf INNER JOIN ItemFolder f ON fbf.FolderGuid = f.ItemGuid LEFT OUTER JOIN ItemPresentation ipf ON ipf.Guid = fbf.FolderGuid WHERE fbf.ParentFolderGuid = @parentGuid AND f.[IsFolder] = 1; WITH Hierarchy AS ( select f.ScopeCollectionGuid, f.ParentScopeCollectionGuid, f.BaseGuid, CAST(s.String as NVARCHAR(1000)) [String] from @folders f INNER JOIN String s ON s.BaseGuid=f.BaseGuid AND s.StringRef='item.name' AND s.Culture='' where f.BaseGuid=@parentGuid UNION ALL select f.ScopeCollectionGuid, f.ParentScopeCollectionGuid, f.BaseGuid, CAST(h.String+'\'+s.String as NVARCHAR(1000)) [String] from @folders f INNER JOIN String s ON s.BaseGuid=f.BaseGuid AND s.StringRef='item.name' AND s.Culture='' inner join Hierarchy h ON f.ParentScopeCollectionGuid = h.ScopeCollectionGuid ) INSERT @OrganizationalGroup SELECT h.ScopeCollectionGuid, h.String FROM Hierarchy h ; /* Example: using the @OrganisationalGroup table variable in a query */ SELECT vi.Guid, vi.Name, og.OrganizationalGroup from vItem vi inner join ScopeMembership sm on sm.ResourceGuid = vi.Guid inner join @OrganizationalGroup og on og.ScopeCollectionGuid = sm.ScopeCollectionGuid
ORDER BY vi.Name ASC;