Execute the following script in Query Editor to create a chart of accounts:

USE AdventureWorks;

-- DROP TABLE MasterAccount
CREATE TABLE MasterAccount(MasterAccountID INT PRIMARY KEY, Parent INT, Balance FLOAT)
GO
-- DROP TABLE ChartOfAccounts
CREATE TABLE ChartOfAccounts(ChartOfAccountsID INT PRIMARY KEY, Parent INT)
GO
INSERT INTO MasterAccount VALUES (3661, 2661, 100500)
INSERT INTO MasterAccount VALUES(46461, 3662, 12000)
INSERT INTO MasterAccount VALUES(46462, 3662, 14000)
INSERT INTO MasterAccount VALUES(36464, 2662, 170500)
INSERT INTO MasterAccount VALUES(36465, 2662, 100500)
INSERT INTO MasterAccount VALUES(36466, 2662, 25000)
INSERT INTO MasterAccount VALUES(4661, 3662, 12000)
INSERT INTO MasterAccount VALUES(4662, 3662, 14000)
INSERT INTO MasterAccount VALUES(3664, 2662, 170500)
INSERT INTO MasterAccount VALUES(3665, 2662, 10000)
INSERT INTO MasterAccount VALUES(3666, 3665, 25000)
INSERT INTO MasterAccount VALUES(3667, 2663, 70500)
INSERT INTO MasterAccount VALUES(3668, 2663, 9000)

INSERT INTO ChartOfAccounts VALUES(3662, 2661)
INSERT INTO ChartOfAccounts VALUES(2661, 1660)
INSERT INTO ChartOfAccounts VALUES(2662, 1660)
INSERT INTO ChartOfAccounts VALUES(3665, 2661)
INSERT INTO ChartOfAccounts VALUES(2663, 1660)
INSERT INTO ChartOfAccounts VALUES(1660, 0)
GO

WITH Rollup(AccountID, Parent, [Type], Balance)
AS
(

SELECT MasterAccountID, Parent,'Master', Balance
FROM MasterAccount
UNION ALL
SELECT R1.ChartOfAccountsID, R1.Parent, 'Rollup', R2.Balance
FROM ChartOfAccounts R1
JOIN Rollup R2
ON R1.ChartOfAccountsID = R2.Parent
)

SELECT Account = AccountID, [Type],
AccountBalance=SUM(Balance)
FROM Rollup
GROUP BY [Type], AccountID
ORDER BY [Type], AccountID
GO