Archive

Posts Tagged ‘SQL’

Recursive queries with hierarchical result using SQL Server

February 10th, 2014 No comments

Recursive queries are not commonly used, yet they are very useful to display hierarchical data stored in flat tables.

Unlike the “Connect by” statement in Oracle, SQL Server doesn’t provide an easy answer to build them.

Here is a way to solve this with SQL Server, found after a bit of reading.

Lets use a flat table named “Example” containing some hierarchical data with the following column:

IDNameParentID
1Root NameNULL
2child11
3child21
4Grandchild12
5Crandchild22
6Grandchild13

In this table, any row can have a parent (except the root one), and therefore a hierarchy view could be created:

Root: Level 1Level 2Level 3
Root Name
Child1
Grandchild1
Grandchild2
Child2
Grandchild1

In order to create a query to display this hierarchical view, we first create a recursive source (‘recursive_source’) to provide the expected result. We will need the following 4 columns in our result:

  1. ID: a unique identifier used for joining itself recursively to the parent ID,
  2. The name: this is what we want to display in our report,
  3. The depth level: to be able to indent the result nicely
  4. A sort field: to ensure we display the data in proper hierarchical order

WITH recursive_source( ID, Name, Depth_Level, Sort) AS

(

The first selection is only to get the starting point, the root of our hierarchy (level 1), with the “Parent Name”:

Select    root.ID,

convert(varchar(255), root.name), /* The name, we need no discrepancy in the column type so we convert this into varchar */

1, /* depth level: this is the first iteration, therefore we set it at level 1 */

convert(varchar(255), root.name) /* we will start with the parent name for sorting our output */

from Example as root /* we name this first iteration table "root" */

Where root.ID = /* either know the parent ID, or prompt users to select where they would like to start the hierarchical report from */

This is where we create the recursive query

UNION ALL

Select    iteration.ID,

convert(varchar(255), REPLICATE('|---', Depth_Level) + iteration.Name), /* Add "|---" as many time as Depth_Level (= Replicate) in front of the name, this creates a natural indentation of the result */

Depth_Level + 1,  /* increment Depth_Level for next iteration */

convert(varchar(255), RTRIM(Sort) + '|---' + iteration.Name) /* Append the new name at the end of the sort field + "|---" to keep the hierarchical order of the data as previous level are in front of the name */

From Example as iteration, recursive_source /* The source is recursively queried */

where recursive_source.ID = iteration.ParentID

)

Then create the select statement which will display the sorted recursive result with hierarchical indentation:

Select Name 'Hierarchical Structure'

from recursive_source

order by Sort

Here is the full query in one block an no comment:

WITH recursive_source( ID, Name, Depth_Level, Sort) AS

(Select  root.ID,

convert(varchar(255), root.name),

1,

convert(varchar(255), root.name)

from Example as root

where root.ID =

UNION ALL

Select    iteration.ID,

convert(varchar(255),

REPLICATE('|---', Depth_Level) + iteration.Name),

Depth_Level + 1,

convert(varchar(255), RTRIM(Sort) + '|---' + iteration.Name)

From Example as iteration, recursive_source

where recursive_source.ID = iteration.ParentID

)

Select Name 'Hierarchical Structure'

from recursive_source

order by Sort