I am working in SQL Server 2014. I have a strange data hierarchy situation. (At least, I haven't experienced something like it before.)
In my hierarchy, there are several root / highest-level objects. Every object below the root level maps to one and only one object above it. Not every node path is the same length. For example, one path could have 2 object levels in it, whereas another path could have 20 object levels in it.
Every object in the hierarchy has an IsInherited attribute, as well as some other attribute (call it SomeAttribute). The IsInherited attribute indicates if the given object inherits the value of SomeAttribute from its most immediate parent. Naturally, if an object's IsInherited attribute is 'Y', then, the given object inherits the value of SomeAttribute from its most immediate parent (which could, in turn, inherit its value for its most immediate parent, etc.). Otherwise, the object's SomeAttribute value is specified.
Now, all of the above isn't necessarily uncommon. What makes this situation uncommon is the particular implementation of this inheritance. This hierarchy is stored in a single table (does this make this an adjacency list model?), and the value of SomeAttribute is not populated for the given object / row if its IsInherited attribute is 'Y'.
My goal is to return the value of SomeAttribute for all objects in the hierarchy.
A sample of the table is as follows:
CREATE TABLE hierarchy (
ID int NOT NULL
,ParentID int NULL
,SomeAttribute char(1) NULL
,IsInherited char(1) NOT NULL
)
;
INSERT INTO hierarchy (ID, ParentID, SomeAttribute, IsInherited)
VALUES
(1, NULL, 'a', 'N')
,(2, NULL, 'b', 'N')
,(3, NULL, 'c', 'N')
,(4, NULL, 'd', 'N')
,(5, NULL, 'e', 'N')
,(6, NULL, 'f', 'N')
,(7, NULL, 'g', 'N')
,(8, 2, NULL, 'Y')
,(9, 3, 'h', 'N')
,(10, 4, NULL, 'Y')
,(11, 5, 'j', 'N')
,(12, 6, NULL, 'Y')
,(13, 6, 'k', 'N')
,(14, 7, 'l', 'N')
,(15, 7, 'm', 'N')
,(16, 10, NULL, 'Y')
,(17, 11, NULL, 'Y')
,(18, 16, NULL, 'Y')
,(19, 17, NULL, 'Y')
,(20, 19, 'o', 'N')
;
This gives us the following node paths:
1
2-8
3-9
4-10-16-18
5-11-17-19-20
6-12,13
7-14,15
So, with this sample table, I expect to return:
ID SomeAttribute
1 a
2 b
3 c
4 d
5 e
6 f
7 g
8 b (inherited from 2)
9 h
10 d (inherited from 4)
11 j
12 f (inherited from 6)
13 k
14 l
15 m
16 d (inherited from 10, inherited from 4)
17 j (inherited from 11)
18 d (inherited from 16, inherited from 10, inherited from 4)
19 j (inherited from 17, inherited from 11)
20 o
I know that this probably requires recursive CTEs. I'm struggling to write the SQL for this. How do I return the output I want?
This is the Adjacency List model because each row represents a pair of adjacent nodes.
And a recursive CTE would look like this :
with q as
(
select id, SomeAttribute, cast(id as varchar(max)) SomeAttributePath
from hierarchy
where ParentID is null
union all
select c.id, case when c.IsInherited = 'Y' then q.SomeAttribute else c.SomeAttribute end as SomeAttribute, cast(concat(q.SomeAttributePath,'-',c.id) as varchar(max))
from q
join hierarchy c
on c.ParentID = q.ID
)
select *
from q
order by id
Outputing:
id SomeAttribute SomeAttributePath
----------- ------------- -----------------
1 a 1
2 b 2
3 c 3
4 d 4
5 e 5
6 f 6
7 g 7
8 b 2-8
9 h 3-9
10 d 4-10
11 j 5-11
12 f 6-12
13 k 6-13
14 l 7-14
15 m 7-15
16 d 4-10-16
17 j 5-11-17
18 d 4-10-16-18
19 j 5-11-17-19
20 o 5-11-17-19-20