clear markdown compare help best fiddles feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3601491 fiddles created (47964 in the last week).

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 ChartId, [Number], Serial, FIRST_VALUE([ChartId]) OVER (PARTITION BY [Number] ORDER BY [Number], Serial) FROM @Chart; /* 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; */ ;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; SELECT * FROM @Chart;
ChartId Number Serial (No column name)
1 1 1
2 10 2
3 101 3
4 1013 4
5 1013 00001 4
6 1013 00002 4
7 1013 00003 4
8 4 8
9 40 9
10 401 10
11 4011 11
12 4011 00101 11
13 4011 00102 11
14 4011 00103 11
15 4011 00104 11
16 4011 00105 11
17 4011 00105 11
18 41 18
19 411 19
20 4111 20
21 4111 00101 20
22 4111 00102 20
23 4111 00103 20
24 4111 00104 20
25 4111 00105 20
26 4111 00106 20
27 4111 00107 20
ChartId ParentId Number Serial AccountName CurrencyId cType TotalDebit TotalCredit
1 1 CAPITAL & LONG TERM LIABILITIES 1 T 0.00 0.00
2 1 10 CAPITAL & LONG TERM LIABILITIES 1 T 0.00 0.00
3 2 101 CAPITAL & LONG TERM LIABILITIES 1 T 0.00 0.00
4 3 1013 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 7 4 SUPPLIERS 1 T 0.00 0.00
9 8 40 SUPPLIERS 1 T 0.00 0.00
10 9 401 SUPPLIERS 1 T 0.00 0.00
11 10 4011 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 17 41 CUSTOMERS 1 T 0.00 0.00
19 18 411 CUSTOMERS 1 T 0.00 0.00
20 19 4111 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
 hidden batch(es)