Setting up a table with hierarchical data in SQL Server 2014

Regarding hierarchical data in SQL Servers please review this post .

Here is an example of hierarchical data:

Hierarchy_00

This post shows how to create a table; populate and query the table containing hierarchical data.

The data shown in the above figure can be placed in a SQL Server Table as shown which is displaying the graphic user interface table designer in SQL Server 2014.

familyHierarchy_00

This table FamilyHierarchy has three columns (Level in the hierarchy, Name and the hierarchy type) and can be populated using the following script:

Insert FamilyHierarchy
Values
(/1/, 'John_Mary', 'parents'),
(/1/1/, 'Tom', 'Children'),
(/1/2/, 'Joan', 'Children'),
(/1/3/, 'Sam', 'Children'),
(/1/1/1/, 'Lily', '2ndGen'),
(/1/1/2/, 'Jim', '2ndGen'),
(/1/1/3/, 'Beth', '2ndGen'),
(/1/2/1/, 'Rose', '2ndGen'),
(1/2/2/, 'Tim', '2ndGen'),
(1/3/1/, 'Warren','2ndGen')

The table gets populated and can be queried as shown.

familyHierarchy_01