SQL to generate Gnucash sub accounts list in cross tabulation format

[T C]

Hi everyone-
Just wanted to throw this (below) SQL solution out there, in case folks needed to generated a cross tabulation table of nested sub accounts in Gnucash. The SQL code below assumes there are up to four levels of sub accounts. You'll have to modify it if you are interested in more or less levels of sub accounts. Also, the SQL code below assumes you're only interested in four top level accounts: "Assets", "Credit", "Expenses", "Income". Again, you can modify as need. If you save your Gnucash file as a SQLite file and download a SQLite editor such as SQLiteStudio, you can run the SQL below to build an accounts cross tabulation (spreadsheet) table of sub accounts:
DROP TABLE IF EXISTS accounts_xtb;
CREATE TABLE accounts_xtb AS 
SELECT   --SUB LEVEL 5 
    L1_name
    , L1_guid
    , L2_name
    , L2_guid
    , L3_name
    , L3_guid
    , L4_name
    , L4_guid
    , ac5.name AS L5_name
    , ac5.guid AS L5_guid
FROM (
    --SUB LEVEL 4
    SELECT
        L1_name
        , L1_guid
        , L2_name
        , L2_guid
        , L3_name
        , L3_guid
        , ac4.name AS L4_name
        , ac4.guid AS L4_guid
    FROM (
        --SUB LEVEL 3
        SELECT
            L1_name
            , L1_guid
            , L2_name
            , L2_guid
            , ac3.name AS L3_name
            , ac3.guid AS L3_guid
        FROM (
            --SUB LEVEL 2
            SELECT
                L1.name AS L1_name
                , L1.guid AS L1_guid
                , ac.guid AS L2_guid
                , ac.name AS L2_name
            FROM (
                --TOP LEVEL 1
                SELECT name, guid FROM accounts WHERE name IN ("Assets", "Credit", "Expenses", "Income")
            ) AS L1
            LEFT JOIN accounts AS ac  ON ac.parent_guid = L1.guid
        ) AS L2
        LEFT JOIN accounts AS ac3 ON ac3.parent_guid = L2.L2_guid
    ) AS L3 
    LEFT JOIN accounts AS ac4
    ON ac4.parent_guid = L3.L3_guid
) AS L4
LEFT JOIN accounts AS ac5
ON ac5.parent_guid = L4.L4_guid
ORDER BY L1_name, L2_name, L3_name, L4_name, ac5.name;

Search terms:Accounts crosstabAccounts crosstabulationAccounts cross tabulation
SQLsqlitesqlite3Accounts spreadsheetAccounts subaccountsAccounts sub accountsAccounts nestedSubaccount columnssub-accounts