Ed-Fi ResourceClaims->Resources

resourceclaims.sql

WITH 
Q AS (
  SELECT ResourceClaimId, ResourceName AS ResourceClaim, ParentResourceClaimId, 0 As Lvl FROM ResourceClaims WHERE ParentResourceClaimId IS NULL
  UNION ALL
  SELECT ResourceClaims.ResourceClaimId, ResourceClaims.ResourceName, ResourceClaims.ParentResourceClaimId, Lvl+1
  FROM ResourceClaims JOIN Q ON ResourceClaims.ParentResourceClaimId = Q.ResourceClaimId
)
SELECT Q.ResourceClaim, STUFF((SELECT ', ' + Q1.ResourceClaim FROM Q Q1 WHERE Q.ResourceClaimId = Q1.ParentResourceClaimId ORDER BY Q1.ResourceClaim FOR XML PATH('')), 1, 2, '') AS Resources
  FROM Q
  WHERE Q.Lvl = 0
  GROUP BY Q.ResourceClaimId, Q.ResourceClaim
  ORDER BY Q.ResourceClaim