CREATE TABLE Master
([Act_Code] int, [Name] varchar(17), [Parent_Act] int)
;
INSERT INTO Master
([Act_Code], [Name], [Parent_Act])
VALUES
(1, 'Asset', null),
(2, 'Cash', 1),
(3, 'A/Rcv', 1),
(4, 'PettyCash', 2),
(5, 'ShopPettyCash', 4),
(6, 'CashforShpVisitor', 5),
(7, 'Bank', 1),
(8, 'PakBank', 7),
(9, 'HBL', 8),
(10, 'HBL Lhr', 9),
(11, 'HBL Gulberg', 10),
(12, 'ABL Lahr', 8)
;
CREATE TABLE Trans
([Act_Code] int, [Charge_Amt] int)
;
INSERT INTO Trans
([Act_Code], [Charge_Amt])
VALUES
(6, 1000),
(11, 15000),
(6, 2000),
(11, 2500),
(12, 16000)
;
With Summed as (
SELECT m.Act_Code,Name,Parent_Act,Sum(Charge_Amt) Charge_Amt
FROM Master m
Left Outer JOIN Trans t
ON m.Act_Code = t.Act_Code
GROUP BY m.Act_Code,Name,Parent_Act
)
,Recursed as (
SELECT Act_Code,Name,Parent_Act,Charge_Amt
FROM Summed
UNION ALL
SELECT s.Act_Code,s.Name,s.Parent_Act,r.Charge_Amt
FROM Recursed r
JOIN Summed s
ON r.Parent_Act = s.Act_Code
)
SELECT Act_Code,Name,Parent_Act,Sum(Charge_Amt) Charge_Amt
FROM Recursed
GROUP BY Act_Code,Name,Parent_Act
Just proving the concept, you'll have to fix the indentation yourself.