Hierarchy view for rolling up data

Standard

Most Hierarchy code I’ve found relies on having write ability to the source system to write back a hierarchy ancestor table. This is a view to roll up data in a method that is better performing and more flexible than I’ve seen. This should support up to 10 levels of parents. This is designed in an Oracle database, but it should work in any SQL environment. Please make sure there is indexes on the ID columns.

WITH RELATIONSHIP_VALUES AS (
 SELECT [ROW_ID] AS CHILD_ID,
 [ROW_PARENT_ID] AS ANCESTOR_ID
 FROM [DATA_SOURCE]
) SELECT ANCESTORS.CHILD_ID,
 CASE DISTANCE
 WHEN 0 THEN ANCESTOR_ID_0
 WHEN 1 THEN ANCESTOR_ID_1
 WHEN 2 THEN ANCESTOR_ID_2
 WHEN 3 THEN ANCESTOR_ID_3
 WHEN 4 THEN ANCESTOR_ID_4
 WHEN 5 THEN ANCESTOR_ID_5
 WHEN 6 THEN ANCESTOR_ID_6
 WHEN 7 THEN ANCESTOR_ID_7
 WHEN 8 THEN ANCESTOR_ID_8
 WHEN 9 THEN ANCESTOR_ID_9
 WHEN 10 THEN ANCESTOR_ID_10
 END
 AS ANCESTOR_ID,
 DISTANCE,
 CASE WHEN LEAFS.ANCESTOR_ID IS NULL
 THEN 1
 ELSE 0
 END
 AS IS_LEAF
FROM (
 SELECT DISTANCE_0.CHILD_ID,
 CASE WHEN DISTANCE_9.ANCESTOR_ID IS NOT NULL THEN 10
 WHEN DISTANCE_8.ANCESTOR_ID IS NOT NULL THEN 9
 WHEN DISTANCE_7.ANCESTOR_ID IS NOT NULL THEN 8
 WHEN DISTANCE_6.ANCESTOR_ID IS NOT NULL THEN 7
 WHEN DISTANCE_5.ANCESTOR_ID IS NOT NULL THEN 6
 WHEN DISTANCE_4.ANCESTOR_ID IS NOT NULL THEN 5
 WHEN DISTANCE_3.ANCESTOR_ID IS NOT NULL THEN 4
 WHEN DISTANCE_2.ANCESTOR_ID IS NOT NULL THEN 3
 WHEN DISTANCE_1.ANCESTOR_ID IS NOT NULL THEN 2
 WHEN DISTANCE_0.ANCESTOR_ID IS NOT NULL THEN 1
 END
 AS MAX_DISTANCE,
 DISTANCE_0.CHILD_ID AS ANCESTOR_ID_0,
 DISTANCE_0.ANCESTOR_ID AS ANCESTOR_ID_1,
 DISTANCE_1.ANCESTOR_ID AS ANCESTOR_ID_2,
 DISTANCE_2.ANCESTOR_ID AS ANCESTOR_ID_3,
 DISTANCE_3.ANCESTOR_ID AS ANCESTOR_ID_4,
 DISTANCE_4.ANCESTOR_ID AS ANCESTOR_ID_5,
 DISTANCE_5.ANCESTOR_ID AS ANCESTOR_ID_6,
 DISTANCE_6.ANCESTOR_ID AS ANCESTOR_ID_7,
 DISTANCE_7.ANCESTOR_ID AS ANCESTOR_ID_8,
 DISTANCE_8.ANCESTOR_ID AS ANCESTOR_ID_9,
 DISTANCE_9.ANCESTOR_ID AS ANCESTOR_ID_10
 FROM RELATIONSHIP_VALUES DISTANCE_0
 LEFT JOIN RELATIONSHIP_VALUES DISTANCE_1
 ON DISTANCE_0.ANCESTOR_ID = DISTANCE_1.CHILD_ID
 LEFT JOIN RELATIONSHIP_VALUES DISTANCE_2
 ON DISTANCE_1.ANCESTOR_ID = DISTANCE_2.CHILD_ID
 LEFT JOIN RELATIONSHIP_VALUES DISTANCE_3
 ON DISTANCE_2.ANCESTOR_ID = DISTANCE_3.CHILD_ID
 LEFT JOIN RELATIONSHIP_VALUES DISTANCE_4
 ON DISTANCE_3.ANCESTOR_ID = DISTANCE_4.CHILD_ID
 LEFT JOIN RELATIONSHIP_VALUES DISTANCE_5
 ON DISTANCE_4.ANCESTOR_ID = DISTANCE_5.CHILD_ID
 LEFT JOIN RELATIONSHIP_VALUES DISTANCE_6
 ON DISTANCE_5.ANCESTOR_ID = DISTANCE_6.CHILD_ID
 LEFT JOIN RELATIONSHIP_VALUES DISTANCE_7
 ON DISTANCE_6.ANCESTOR_ID = DISTANCE_7.CHILD_ID
 LEFT JOIN RELATIONSHIP_VALUES DISTANCE_8
 ON DISTANCE_7.ANCESTOR_ID = DISTANCE_8.CHILD_ID
 LEFT JOIN RELATIONSHIP_VALUES DISTANCE_9
 ON DISTANCE_8.ANCESTOR_ID = DISTANCE_9.CHILD_ID
 ) ANCESTORS
LEFT JOIN (
 SELECT ANCESTOR_ID
 FROM RELATIONSHIP_VALUES
 ) LEAFS
ON
 ANCESTORS.CHILD_ID = LEAFS.ANCESTOR_ID
LEFT JOIN (
 SELECT 0 AS DISTANCE
 FROM DUAL
 UNION
 SELECT 1 AS DISTANCE
 FROM DUAL
 UNION
 SELECT 2 AS DISTANCE
 FROM DUAL
 UNION
 SELECT 3 AS DISTANCE
 FROM DUAL
 UNION
 SELECT 4 AS DISTANCE
 FROM DUAL
 UNION
 SELECT 5 AS DISTANCE
 FROM DUAL
 UNION
 SELECT 6 AS DISTANCE
 FROM DUAL
 UNION
 SELECT 7 AS DISTANCE
 FROM DUAL
 UNION
 SELECT 8 AS DISTANCE
 FROM DUAL
 UNION
 SELECT 9 AS DISTANCE
 FROM DUAL
 UNION
 SELECT 10 AS DISTANCE
 FROM DUAL
 ) LEVEL_DISTANCES
ON
 LEVEL_DISTANCES.DISTANCE <= ANCESTORS.MAX_DISTANCE
ORDER BY 1,3;
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s