CODEWITHSUNDEEP
sqlsql-serversql-server-2017
Dmitry
Dmitry

Reputation: 315

Split data by levels in hierarchy

Example of initial data:

| ID   |  ParentID  |
|------|------------|
|  1   |    NULL    |
|  2   |     1      |
|  3   |     1      |
|  4   |     2      |
|  5   |    NULL    |
|  6   |     2      |
|  7   |     3      |

In my initial data I have ID of element and his parent ID. Some elements has parent, some has not, some has a parent and his parent has a parent.

The maximum number of levels in this hierarchy is 3.

I need to get this hierarchy by levels.

Lvl 1 - elements without parents Lvl 2 - elements with parent which doesn't have parent Lvl 3 - elements with parent which has a parent too.

Expected result looks like:

| Lvl1  |   Lvl2   |   Lvl3   |
|-------|----------|----------|
|  1    |   NULL   |   NULL   |
|  1    |    2     |   NULL   |
|  1    |    3     |   NULL   |
|  1    |    2     |    4     |
|  5    |   NULL   |   NULL   |
|  1    |    2     |    6     |
|  1    |    3     |    7     |

How I can do it?

Upvotes: 2

Views: 555

Answers (1)

MatBailie
MatBailie

Reputation: 86745

For a fixed dept of three, you can use CROSS APPLY.

It can be used like a JOIN, but also return extra records to give you the NULLs.

SELECT
  Lvl1.ID   AS lvl1,
  Lvl2.ID   AS lvl2,
  Lvl3.ID   AS lvl3
FROM
  initial_data   AS Lvl1
CROSS APPLY
(
   SELECT ID FROM initial_data WHERE ParentID = Lvl1.ID
   UNION ALL
   SELECT NULL AS ID
)
  AS Lvl2
CROSS APPLY
(
   SELECT ID FROM initial_data WHERE ParentID = Lvl2.ID
   UNION ALL
   SELECT NULL AS ID
)
  AS Lvl3
WHERE
  Lvl1.ParentID IS NULL
ORDER BY
  Lvl1.ID,
  Lvl2.ID,
  Lvl3.ID

But, as per my comment, this is often a sign that you're headed down a non-sql route. It might feel easier to start with, but later it turns and bites you, because SQL benefits tremendously from normalised structures (your starting data).

Upvotes: 3

Related Questions