-- create sample data

DROP TABLE #ChartOfAccount

CREATE TABLE #ChartOfAccount (Account INT, [Description] VARCHAR(30), Parent INT)

INSERT INTO #ChartOfAccount (Account, [Description], Parent)

SELECT 1, 'ASSETS', 0 UNION ALL

SELECT 101, 'CURRENT ASSETS', 1 UNION ALL

SELECT 10100, 'Cash and Bank', 101 UNION ALL

SELECT 10101, 'Petty Cash', 10100 UNION ALL

SELECT 10301, 'Cash on Hand', 10100 UNION ALL

SELECT 10501, 'Bank', 10100 UNION ALL

SELECT 10701, 'Time Deposit', 10100;

DROP TABLE #Transaction

CREATE TABLE #Transaction (Account INT, GroupID INT, BankID INT, Amount INT) -- (only child account)

INSERT INTO #Transaction (Account, GroupID, BankID, Amount)

SELECT 10101, 2, 1, 10 UNION ALL

SELECT 10101, 2, 2, 20 UNION ALL

SELECT 10301, 2, 1, 10 UNION ALL

SELECT 10501, 2, 1, 10 UNION ALL

SELECT 10501, 2, 2, 20 UNION ALL

SELECT 10701, 2, 2, 30;

-- solution with sample data provided

;WITH rCTE AS (

SELECT [Level] = 1, c.Parent AS Account, t.GroupID, t.BankID, Amount = SUM(t.Amount)

FROM #Transaction t

INNER JOIN #ChartOfAccount c ON c.Account = t.Account

GROUP BY c.Parent, t.GroupID, t.BankID

UNION ALL

SELECT [Level] = lr.[Level] + 1, tr.Parent AS Account, lr.GroupID, lr.BankID, Amount

FROM rCTE lr

INNER JOIN #ChartOfAccount tr ON tr.Account = lr.Account AND Parent > 0

)

SELECT [Level],

Account, GroupID, BankID, Amount

FROM rCTE

UNION ALL

SELECT [Level] = 0,

Account, GroupID, BankID, Amount

FROM #Transaction

ORDER BY Account, GroupID, BankID