Create sql query to inherits value from nearest parent row

Question

I have a self-referenced table called Units that has a "BossId" column refers to manager person . There is business rule to determine the unit's Boss as described below:

1-The unit has its own BossId. (there is no more work)

2-The BossId is null. in this case we refer to the most nearest parent that has bossId value

i wanna create an efficient SQL view that all unit and their boss is specified according to the mentioned rules below is the structure of my unit table:

CREATE TABLE [dbo].[Unit](
[Id] [int] IDENTITY(1,1) NOT NULL,
[ParentId] [int] NULL,
[BossId] [int] NULL,

Here is the sample data:

INSERT INTO Units (ID, ParentID, BossId) VALUES (1, NULL, 1000)
INSERT INTO Units (ID, ParentID, BossId) VALUES (2, 1, NULL)
INSERT INTO Units (ID, ParentID, BossId) VALUES (3, 2, NULL)
INSERT INTO Units (ID, ParentID, BossId) VALUES (4, 1, 3000)
INSERT INTO Units (ID, ParentID, BossId) VALUES (5, 4, NULL)

Selecting the data as follows:

Select ID,ParentId,BossId from Units

result would be:

+----+-------+----------+
| ID | ParentId| BossId|
+----+-------+----------+
|  1 | NULL  |  1000    |
|  2 | 1     |  NULL    |
|  3 | 2     |  NULL    |
|  4 | 1     |  3000    |
|  5 | 4     |  NULL    |
+----+-------+----------+

I need some view to produce something like this:

+----+-------+----------+
| ID | ParentId| BossId|
+----+-------+----------+
|  1 | NULL  |  1000    |
|  2 | 1     |  1000    |
|  3 | 2     |  1000    |
|  4 | 1     |  3000    |
|  5 | 4     |  3000    |
+----+-------+----------+

So all unit's boss id is specified according to the rule

How about supplying some example data and expected results? You'll get a far better and more accurate result if you help us along first. — Rachel Ambler, Sep 19 '15 at 12:08

score 0 · Answer 1 · answered Sep 19 '15 at 16:08

With _cte (ParentId, Id, BossId)
As
(

    Select   ParentId, Id, BossId
      From   Units

    Union All

    Select   U.parentId, U.Id, c.BossId
    From     Units As U
    Join     _cte As c
      On      u.ParentId = c.Id

)
Select  Id, ParentId, Max(BossId) As BossId
  From  _cte
  Where BossId Is Not Null
  Group
    By  Id, ParentId
  Order
    By  Id, ParentId

Produces

Id          ParentId    BossId
----------- ----------- -----------
1           NULL        1000
2           1           1000
3           2           1000
4           1           3000
5           4           3000

Create sql query to inherits value from nearest parent row

1 Answers1