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

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

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)
as
(
	select 
		name,
		nodeId,  
		0 as depth,
		CAST(nodeId AS nvarchar(max)) AS location
	from tree 
	where parentNodeId is null

	union all

	select 
		cast(concat(space(parent.depth * 5), '|__', child.name) as nvarchar(max)), 
		child.nodeId, 
		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.