CODEWITHSUNDEEP
sqlsql-servercommon-table-expression
Yesudass Moses
Yesudass Moses

Reputation: 1859

Query to group and sort hierarchical data

I have a table like this in my SQL Server 2008.

ID     ParentID    Level     Code    Name           Description
1      1           1         EXP     Expenses       --
2      1           2         PEXP    Project Exp    --
3      1           2         IEXP    Indirect Exp.  --
4      4           1         INC     Incomes        --
5      1           2         MEXP    Misc. Exp.     --
6      2           3         MCOST   Material Cost  --
7      4           2         IINC    Indirect Inc.  --
8      6           4         TCOS    Tiles Cost    --

I want a query which select all the rows in hierarchical order. (Tiles Cost under Material Cost, Material Cost under Project Expense, Project Expenses under Expenses etc. There can be at most 5 levels. There are 2000 rows in the table.)

Is this possible in SQL query?

The expected result should look like this:

ID     ParentID    Level     Code    Name           Description
1      1           1         EXP     Expenses       --
3      1           2         IEXP    Indirect Exp.  --
5      1           2         MEXP    Misc. Exp.     --
2      1           2         PEXP    Project Exp    --
6      2           3         MCOST   Material Cost  --
8      6           4         TCOS    Tiles Cost    --
4      4           1         INC     Incomes        --
7      4           2         IINC    Indirect Inc.  --

Upvotes: 1

Views: 1493

Answers (2)

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

You can use a Recursive CTE :

;WITH CTE_Tree AS (
   -- Anchor member: get all parent nodes, initialize order_key
   SELECT ID, ParentID, Level, Code, Name, 
          CAST(ID AS VARCHAR(MAX)) AS order_key
   FROM mytable
   WHERE Level = 1

   UNION ALL

   -- Recursive member: get child node of previous node and update
   -- order _key of branch
   SELECT t1.ID, t1.ParentID, t1.Level, t1.Code, t1.Name,
          order_key = t2.order_key + '.' + CAST(t1.Level AS VARCHAR(MAX))
   FROM mytable AS t1
   INNER JOIN CTE_Tree AS t2 ON t1.ParentID = t2.ID AND t1.Level > t2.Level
)
SELECT *
FROM CTE_Tree
ORDER BY order_key,Code

The CTE is used so as to recursively calculate an order key. The first digit of the order key is the ID of the parent node. This is because we want all nodes from branch of first parent ordered above nodes of branch of next parent.

Second, third, etc., digit is simply the level of the node. This way level 2 nodes are ordered exactly after the parent node, level 3 nodes come next, etc.

Demo here

Upvotes: 1

Daxmann
Daxmann

Reputation: 46

Based on the fact that you have a maximum of 5 levels, this should work.

If you select only the values on the first level and then use inner joins to go down the levels, you should get your expected result:

SELECT MyTable5.* FROM MyTable as MyTable1 
  INNER JOIN MyTable AS MyTable2 ON MyTable2.ParentId = MyTable1.Id
  INNER JOIN MyTable AS MyTable3 ON MyTable3.ParentId = MyTable2.Id
  INNER JOIN MyTable AS MyTable4 ON MyTable4.ParentId = MyTable3.Id
  INNER JOIN MyTable AS MyTable5 ON MyTable5.ParentId = MyTable4.Id
WHERE MyTable1.Level = 1
ORDER BY MyTable1.Id, MyTable5.Level

Upvotes: 0

Related Questions