[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)
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.

  • Ravi Rawat

    CREATE TABLE table2
    (
    id INT AUTO_INCREMENT PRIMARY KEY,
    NAME VARCHAR(20),
    parent_id INT(20)
    )

    INSERT INTO table2 (NAME) VALUES (‘cloth’)
    INSERT INTO table2 (NAME) VALUES (‘shoe’)

    INSERT INTO table2 (NAME,parent_id) VALUES (‘denim’,1)
    INSERT INTO table2 (NAME,parent_id) VALUES (‘shirt’,3)
    INSERT INTO table2 (NAME,parent_id) VALUES (‘pant’,3)

    i want to do the same for this please help and i am doing this in MYSQL

    • Hi Ravi,

      You can simple replace the table and column names on the CTE (Common Table Expression), something like this:

      ;with orderedTree (name, id, depth, location)
      as
      (
      select
      name,
      id,
      0 as depth,
      CAST(id AS nvarchar(max)) AS location
      from table2
      where parent_id is null

      union all

      select
      cast(concat(space(parent.depth * 5), ‘|__’, child.name) as nvarchar(max)),
      child.id,
      parent.depth + 1,
      cast(concat(parent.location, ‘.’ ,child.id) AS nvarchar(max)) AS location
      from table2 child
      inner join orderedTree parent
      on child.parent_id = parent.id

      )

      select * from orderedTree
      order by location

      • Ravi Rawat

        Hi Pablo,
        By following your steps i am getting this error.
        1 queries executed, 0 success, 1 errors, 0 warnings

        Query: with orderedTree (name, id, depth, location) as ( select name, id, 0 as depth, CAST(id AS nvarchar(max)) AS location from table2…

        Error Code: 1064
        You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘orderedTree (name, id, depth, location)
        as
        (
        select name, id, 0 as depth, CAST(i’ at line 1

        Execution Time : 0 sec
        Transfer Time : 0 sec
        Total Time : 0.001 sec

        • Oh I see, you are using MySQL. The post was about Microsoft SQL Server. I advise you to post your specific question on stackoverflow.com