1

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

1

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

  • Thank you McNets, that was awesome solution... you saved my life... – Adel Merhej Jan 18 at 1:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.