Execute the following Microsoft SQL Server T-SQL script in SSMS Query Editor to create a chart of accounts:

-- T-SQL CTE - Common Table Expression
-- Charts of Accounts - Accounting term

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

/*

ACCOUNT     Type  AccountBalance

3661        Master      100500

3664        Master      170500

3665        Master      10000

3666        Master      25000

3667        Master      70500

3668        Master      9000

4661        Master      12000

4662        Master      14000

36464       Master      170500

36465       Master      100500

36466       Master      25000

46461       Master      12000

46462       Master      14000

1660        Rollup      733500

2661        Rollup      177500

2662        Rollup      476500

2663        Rollup      79500

3662        Rollup      52000

3665        Rollup      25000

*/