Transposing 'Accounts' hierarchy to flat table

Question

Wanting to create some reporting on my GnuCash DB in MariaDB, I'm trying to transpose the accounts table into a flat table. In essence, the Accounts table contains each Account with an ID (guid) as well as a PARENT_ID (parent_guid). The top account has an account_type = 'ROOT' and is named 'Root Account'.

Knowing I have a maximum of 4 levels down from the top account (i.e. 5 levels including 'Root Account') I'm looking to translate this to a table having the following columns:

• Account ID (guid)
• Account name (name)
• Level 1 (The Account Name 1 level down from the 'Root Account')
• Level 2 (The Account Name 2 levels down from the 'Root Account')
• Level 3 (The Account Name 3 levels down from the 'Root Account')
• Level 4 (The Account Name 4 levels down from the 'Root Account')

The accounts table definition:

CREATE TABLE `accounts` (
    `guid` VARCHAR(32) NOT NULL COLLATE 'utf8mb3_general_ci',
    `name` VARCHAR(2048) NOT NULL COLLATE 'utf8mb3_general_ci',
    `account_type` VARCHAR(2048) NOT NULL COLLATE 'utf8mb3_general_ci',
    `commodity_guid` VARCHAR(32) NULL DEFAULT NULL COLLATE 'utf8mb3_general_ci',
    `commodity_scu` INT(11) NOT NULL,
    `non_std_scu` INT(11) NOT NULL,
    `parent_guid` VARCHAR(32) NULL DEFAULT NULL COLLATE 'utf8mb3_general_ci',
    `code` VARCHAR(2048) NULL DEFAULT NULL COLLATE 'utf8mb3_general_ci',
    `description` VARCHAR(2048) NULL DEFAULT NULL COLLATE 'utf8mb3_general_ci',
    `hidden` INT(11) NULL DEFAULT NULL,
    `placeholder` INT(11) NULL DEFAULT NULL,
    PRIMARY KEY (`guid`) USING BTREE
)
COLLATE='utf8mb3_general_ci'
ENGINE=InnoDB
;

My solution:

SELECT a1.guid AS a_guid,
            a1.name AS L1,
            NULL AS L2,
            NULL AS L3,
            NULL AS L4,
            IF(a1.name LIKE '%Inkomen', -1, 1) AS multiplier
FROM gnucash.accounts a1
JOIN gnucash.accounts a0 ON a0.guid = a1.parent_guid AND a0.name = 'Root Account'

UNION ALL

SELECT a2.guid AS a_guid,
            a1.name AS L1,
            a2.name AS L2,
            NULL AS L3,
            NULL AS L4,
            IF(a1.name LIKE '%Inkomen', -1, 1) AS multiplier
FROM gnucash.accounts a2
JOIN gnucash.accounts a1 ON a1.guid = a2.parent_guid
JOIN gnucash.accounts a0 ON a0.guid = a1.parent_guid AND a0.name = 'Root Account'

UNION ALL

SELECT a3.guid AS a_guid,
            a1.name AS L1,
            a2.name AS L2,
            a3.name AS L3,
            NULL AS L4,
            IF(a1.name LIKE '%Inkomen', -1, 1) AS multiplier
FROM gnucash.accounts a3
JOIN gnucash.accounts a2 ON a2.guid = a3.parent_guid
JOIN gnucash.accounts a1 ON a1.guid = a2.parent_guid
JOIN gnucash.accounts a0 ON a0.guid = a1.parent_guid AND a0.name = 'Root Account'

UNION ALL

SELECT a4.guid AS a_guid,
            a1.name AS L1,
            a2.name AS L2,
            a3.name AS L3,
            a4.name AS L4,
            IF(a1.name LIKE '%Inkomen', -1, 1) AS multiplier
FROM gnucash.accounts a4
JOIN gnucash.accounts a3 ON a3.guid = a4.parent_guid
JOIN gnucash.accounts a2 ON a2.guid = a3.parent_guid
JOIN gnucash.accounts a1 ON a1.guid = a2.parent_guid
JOIN gnucash.accounts a0 ON a0.guid = a1.parent_guid AND a0.name = 'Root Account'

Which makes me wonder: isn't there a more efficient and elegant way to create this view? I've tried different JOINs, but haven't been able to avoid the UNION of each level.

I've tried How to create a MySQL hierarchical recursive query?, but this just gives me a list of all accounts related to one top-level parent. I'm trying to convert it to a table: | guid | name | Level 1 | Level 2 | Level 3 | Level 4 | And I'm trying to have all accounts be included, including mid-level accounts (like my solution does)

• @MatBailie #1: sorry, I should've just copied the cte. Adjusted.
• @MatBailie #2: I don't control the underlying database design for GnuCash, hence I can't change the use of guid.
• @MatBailie #3: thank you.
• @Bill Karwin: 10.11.4-MariaDB-1:10.11.4+maria~ubu2204