Sorting Table in hierarchical order

Question

Is it possible to sorting queries table in hierarchical order like this:

Expected

+----+--------+-----------+-------+--------+-----------+-----------+---------+
| ID | Code   |  Name     | Qty   | Amount | is_parent | parent_id | remarks |
+----+--------+-----------+-------+--------+-----------+-----------+---------+
| 1  | ABC    | Parent1   |  2    | 1,000  |     1     |     0     | xxx     |
+----+--------+-----------+-------+--------+-----------+-----------+---------+
| 4  | FFLK   | Product Z |  10   | 2,500  |     0     |     1     | xxx     |
+----+--------+-----------+-------+--------+-----------+-----------+---------+
| 5  | P6DT   | Product 5 |  7    | 1,700  |     0     |     1     | xxx     |
+----+--------+-----------+-------+--------+-----------+-----------+---------+
| 6  | P2GL   | Product T |  5    | 1,100  |     0     |     1     | xxx     |
+----+--------+-----------+-------+--------+-----------+-----------+---------+
| 2  | DHG    | Parent2   |  5    | 1,500  |     1     |     0     | xxx     |
+----+--------+-----------+-------+--------+-----------+-----------+---------+
| 3  | LMSJ   | Product U |  4    | 600    |     0     |     2     | xxx     |
+----+--------+-----------+-------+--------+-----------+-----------+---------+

This is the original data table:

+----+--------+-----------+-------+--------+-----------+-----------+---------+
| ID | Code   |  Name     | Qty   | Amount | is_parent | parent_id | remarks |
+----+--------+-----------+-------+--------+-----------+-----------+---------+
| 1  | ABC    | Parent1   |  2    | 1,000  |     1     |     0     | xxx     |
+----+--------+-----------+-------+--------+-----------+-----------+---------+
| 2  | DHG    | Parent2   |  5    | 1,500  |     1     |     0     | xxx     |
+----+--------+-----------+-------+--------+-----------+-----------+---------+
| 3  | LMSJ   | Product U |  4    | 600    |     0     |     2     | xxx     |
+----+--------+-----------+-------+--------+-----------+-----------+---------+
| 4  | FFLK   | Product Z |  10   | 2,500  |     0     |     1     | xxx     |
+----+--------+-----------+-------+--------+-----------+-----------+---------+
| 5  | P6DT   | Product 5 |  7    | 1,700  |     0     |     1     | xxx     |
+----+--------+-----------+-------+--------+-----------+-----------+---------+
| 6  | P2GL   | Product T |  5    | 1,100  |     0     |     1     | xxx     |
+----+--------+-----------+-------+--------+-----------+-----------+---------+


is_parent column = 1 if data row set to parent, 0 if data row set to child 
parent_id column = 0 if data row set to parent, depend on ID of parent data

I'm using SQL Server to generate the data.

The explanation is unclear. The result though looks like a hierarchical order which can be achieved with a simple `ORDER BY Code` if the key has a sortable format - you'll have to ensure ID values are represented by the same number of digits and padded with `0` if needed. — Panagiotis Kanavos, Oct 04 '19 at 08:55
A better option would be to replace `Code` with a [hierarchyid](https://learn.microsoft.com/en-us/sql/t-sql/data-types/hierarchyid-data-type-method-reference?view=sql-server-2017) field. `hierarchyid` is a binary value whose contents reflect the hierarchy and can be ordered *and* indexed. — Panagiotis Kanavos, Oct 04 '19 at 08:58
what *do* you want to begin with? The explanation is unclear. The results show hierarchical sorting. Right now `Code` seems to be the hierarchy key. Perhaps the *real* question is how to traverse the hierarchy? Either add a `hierarchyid` field or use [a recursive CTE](https://blogs.msdn.microsoft.com/simonince/2007/10/17/hierarchies-with-common-table-expressions/). A hierarchyid is a far faster solution. — Panagiotis Kanavos, Oct 04 '19 at 09:02
Check [Lesson 1: Converting a Table to a Hierarchical Structure](https://learn.microsoft.com/en-us/sql/relational-databases/tables/lesson-1-converting-a-table-to-a-hierarchical-structure?view=sql-server-2017). The [Populate](https://learn.microsoft.com/en-us/sql/relational-databases/tables/lesson-1-converting-a-table-to-a-hierarchical-structure?view=sql-server-2017#populate-the-neworg-table) section contains a recursive query that follows the hierarchy depth-first to generate the new `hierarchyid` values. — Panagiotis Kanavos, Oct 04 '19 at 09:04
@PanagiotisKanavos I edit the data.. now, is it possible to order based on parent and followed the child like above — Joe Wilson, Oct 04 '19 at 09:11

score 0 · Answer 1 · answered Oct 04 '19 at 09:17

It looks like the actual question is how to query the data in hierarchical order. This is possible using recursive queries but a faster alternative is to use SQL Server's support for hierarchical data.

A recursive query that returns the data in hierarchical order would look like this :

WITH h AS
(
      SELECT
             ID,Code,Name,Qty,Amount,is_parent,parent_id,remarks
      FROM
            dbo.ThatTable
      WHERE 
            parent_id=0
      UNION ALL 
      SELECT
             c.ID,c.Code,c.Name,c.Qty,c.Amount,c.is_parent,c.parent_id,c.remarks
      FROM
            dbo.ThatTable c
      INNER JOIN h ON 
            c.parent_id= h.Id
)
SELECT * FROM h

This query's performance will be acceptable if the ID and Parent_ID fields are indexed, but not great.

Adding a hierarchyid field to the table would make the query simpler and far faster. Assuming there's a hierarchy field, the query would be just :

SELECT *
FROM ThatTable
ORDER BY hierarchy

Adding an index on hierarchy will this query and any query that looks eg for children of a specific node, very fast. Instead of querying recursively, the server only needs to look into that single index.

The article Lesson 1: Converting a Table to a Hierarchical Structure shows how to create a new table with a hierarchyid and populate it from parent/child data.

when I applied this query, is not running.. there was appearing error "Incorrect syntax near the keyword 'WITH'." What should I do? There is something that I have to do before running that queries? Should I added one column named hierarchyid to that table? If yes, is that auto-increment? thanks — Joe Wilson, Oct 08 '19 at 05:18

Sorting Table in hierarchical order

1 Answers1