Sunday, July 3, 2016

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:


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.


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
(/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.