[SQL Server] Display hierarchical data from a tree

It is common to have a hierarchical or tree structure table in a SQL model data.

We would like to display the data as follow:

Hierarchical display data

Hierarchical display data

But when it comes to display the raw data it is hard to identify the hierarchical form.

Not hierarchical display

Not hierarchical display

Let’s build a very simple data model for the sake of example.

Create table Tree(
	nodeId int not null primary key,
	parentNodeId int null foreign key references Tree(nodeId),
	name nvarchar(max)

And insert some data.

insert into Tree values
(1, null, 'Root A'),
	(2, 1, 'Child A1'),
		(3, 2, 'Child A11'),
		(4, 2, 'Child A12'),
	(5, 1, 'Child A2'),
		(6, 5, 'Child A21'),
			(7, 6, 'Child A211'),
			(8, 6, 'Child A212'),
		(9, 5, 'Child A22'),
(10, null, 'Root B'),
(11, null, 'Root C'),
	(12, 11, 'Child C1'),
		(13, 12, 'Child C11'),
		(14, 12, 'Child C12'),
	(15, 11, 'Child C2')

Let’s use a Common Table Expression (CTE):

;with orderedTree (name, nodeId, depth, location)
		0 as depth,
		CAST(nodeId AS nvarchar(max)) AS location
	from tree 
	where parentNodeId is null

	union all

		cast(concat(space(parent.depth * 5), '|__', child.name) as nvarchar(max)), 
		parent.depth + 1,
		cast(concat(parent.location, '.' ,child.nodeId) AS nvarchar(max)) AS location
	from tree child
	inner join orderedTree parent
		on child.parentNodeId = parent.nodeId


select * from orderedTree
order by location

Now we can easily see the hierarchical structure of our data.