Sql-server – How to set Parent field according to another hierarchy field

sql serverupdate

I need some help to set a parent field according to hierarchy field like this:

declare @Chart table([ChartId] int NOT NULL identity(1,1), [ParentId] int, [Number] varchar(5), 
    [Serial] varchar(5), [AccountName] varchar(200), CurrencyId int, cType char(1),
    [TotalDebit] decimal(18,2), [TotalCredit] decimal(18,2));

insert into @Chart
select null,  '1    ', null, 'CAPITAL & LONG TERM LIABILITIES', 1, 'T', 0, 0 union all
select null, '10   ', null, 'CAPITAL & LONG TERM LIABILITIES', 1, 'T', 0, 0 union all
select null, '101  ', null, 'CAPITAL & LONG TERM LIABILITIES', 1, 'T', 0, 0 union all
select null, '1013 ', null, 'CAPITAL & LONG TERM LIABILITIES', 1, 'T', 0, 0 union all
select null, '1013 ', '00001', 'ShareHolder 1', 1, 'R', 0, 0 union all
select null, '1013 ', '00002', 'ShareHolder 2', 1, 'R', 0, 0 union all
select null, '1013 ', '00003', 'ShareHolder 3', 1, 'R', 0, 0 union all
select null, '4     ', null, 'SUPPLIERS', 1, 'T', 0, 0 union all
select null, '40   ', null, 'SUPPLIERS', 1, 'T', 0, 0 union all
select null, '401  ', null, 'SUPPLIERS', 1, 'T', 0, 0 union all
select null, '4011 ', null, 'SUPPLIERS', 1, 'T', 0, 0 union all
select null, '4011 ', '00101', 'Supplier 1', 1, 'R', 0, 0 union all
select null, '4011 ', '00102', 'Supplier 1', 2, 'R', 0, 0 union all
select null, '4011 ', '00103', 'Supplier 2', 1, 'R', 0, 0 union all
select null, '4011 ', '00104', 'Supplier 3', 2, 'R', 0, 0 union all
select null, '4011 ', '00105', 'Supplier 3', 1, 'R', 0, 0 union all
select null, '4011 ', '00105', 'Supplier 3', 3, 'R', 0, 0 union all
select null, '41   ', null, 'CUSTOMERS', 1, 'T', 0, 0 union all
select null, '411  ', null, 'CUSTOMERS', 1, 'T', 0, 0 union all
select null, '4111 ', null, 'CUSTOMERS', 1, 'T', 0, 0 union all
select null, '4111 ', '00101', 'Customer 1', 1, 'R', 0, 0 union all
select null, '4111 ', '00102', 'Customer 2', 1, 'R', 0, 0 union all
select null, '4111 ', '00103', 'Customer 2', 2, 'R', 0, 0 union all
select null, '4111 ', '00104', 'Customer 3', 2, 'R', 0, 0 union all
select null, '4111 ', '00105', 'Customer 4', 2, 'R', 0, 0 union all
select null, '4111 ', '00106', 'Customer 4', 3, 'R', 0, 0 union all
select null, '4111 ', '00107', 'Customer 5', 3, 'R', 0, 0;

Select * from @Chart;

What i wish is to update ParentId column as Parent and Id as keyfield and this according to Number Column like: left(number, 1) should be 1 and left(number, 2) should be linked to its parent which is 1… left(number, 3) should be related to its parent …
the output should look like:

Blockquote

I would be highly appreciate any help.

Thank you

Best Answer

Direct update:

UPDATE c0
SET    ParentId = CASE WHEN c0.Serial IS NULL
                  THEN (SELECT TOP 1 c1.ChartId
                        FROM   @Chart c1
                        WHERE  c1.[Number] < c0.[Number]
                               AND c1.Serial IS NULL
                               ORDER BY c1.[Number] DESC)
                  ELSE (SELECT TOP 1 c2.ChartId 
                        FROM   @Chart c2
                        WHERE  c2.[Number] = c0.[Number] 
                        AND    c2.Serial IS NULL)
                  END
FROM   @Chart c0;

Using a CTE to calculate ParentId:

;WITH x AS
(
    SELECT 
        c1.ChartId, 
        CASE WHEN c1.Serial IS NULL
             THEN LAG(c1.ChartId) OVER (ORDER BY c1.[Number], c1.Serial)
             ELSE (FIRST_VALUE([ChartId]) OVER (PARTITION BY [Number] ORDER BY [Number], Serial))
             END AS ParentID
    FROM 
        @Chart c1
)
UPDATE c1
SET    ParentId = x.ParentId
FROM   @Chart c1
JOIN   x 
       ON x.ChartId = c1.ChartId;

ChartId | ParentId | Number | Serial | AccountName                     | CurrencyId | cType | TotalDebit | TotalCredit
------: | -------: | :----- | :----- | :------------------------------ | ---------: | :---- | :--------- | :----------
      1 |     null | 1      | null   | CAPITAL & LONG TERM LIABILITIES |          1 | T     | 0.00       | 0.00       
      2 |        1 | 10     | null   | CAPITAL & LONG TERM LIABILITIES |          1 | T     | 0.00       | 0.00       
      3 |        2 | 101    | null   | CAPITAL & LONG TERM LIABILITIES |          1 | T     | 0.00       | 0.00       
      4 |        3 | 1013   | null   | CAPITAL & LONG TERM LIABILITIES |          1 | T     | 0.00       | 0.00       
      5 |        4 | 1013   | 00001  | ShareHolder 1                   |          1 | R     | 0.00       | 0.00       
      6 |        4 | 1013   | 00002  | ShareHolder 2                   |          1 | R     | 0.00       | 0.00       
      7 |        4 | 1013   | 00003  | ShareHolder 3                   |          1 | R     | 0.00       | 0.00       
      8 |        4 | 4      | null   | SUPPLIERS                       |          1 | T     | 0.00       | 0.00       
      9 |        8 | 40     | null   | SUPPLIERS                       |          1 | T     | 0.00       | 0.00       
     10 |        9 | 401    | null   | SUPPLIERS                       |          1 | T     | 0.00       | 0.00       
     11 |       10 | 4011   | null   | SUPPLIERS                       |          1 | T     | 0.00       | 0.00       
     12 |       11 | 4011   | 00101  | Supplier 1                      |          1 | R     | 0.00       | 0.00       
     13 |       11 | 4011   | 00102  | Supplier 1                      |          2 | R     | 0.00       | 0.00       
     14 |       11 | 4011   | 00103  | Supplier 2                      |          1 | R     | 0.00       | 0.00       
     15 |       11 | 4011   | 00104  | Supplier 3                      |          2 | R     | 0.00       | 0.00       
     16 |       11 | 4011   | 00105  | Supplier 3                      |          1 | R     | 0.00       | 0.00       
     17 |       11 | 4011   | 00105  | Supplier 3                      |          3 | R     | 0.00       | 0.00       
     18 |       11 | 41     | null   | CUSTOMERS                       |          1 | T     | 0.00       | 0.00       
     19 |       18 | 411    | null   | CUSTOMERS                       |          1 | T     | 0.00       | 0.00       
     20 |       19 | 4111   | null   | CUSTOMERS                       |          1 | T     | 0.00       | 0.00       
     21 |       20 | 4111   | 00101  | Customer 1                      |          1 | R     | 0.00       | 0.00       
     22 |       20 | 4111   | 00102  | Customer 2                      |          1 | R     | 0.00       | 0.00       
     23 |       20 | 4111   | 00103  | Customer 2                      |          2 | R     | 0.00       | 0.00       
     24 |       20 | 4111   | 00104  | Customer 3                      |          2 | R     | 0.00       | 0.00       
     25 |       20 | 4111   | 00105  | Customer 4                      |          2 | R     | 0.00       | 0.00       
     26 |       20 | 4111   | 00106  | Customer 4                      |          3 | R     | 0.00       | 0.00       
     27 |       20 | 4111   | 00107  | Customer 5                      |          3 | R     | 0.00       | 0.00

db<>fiddle here

Related Question