SQL to generate Gnucash sub accounts list in cross tabulation format

Wm <Wm... wm_o_o_o at yahoo.co.uk
Tue Sep 20 06:40:15 EDT 2016


On 19/09/2016 18:07, T C via gnucash-user wrote:
> 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

the DROP and CREATE isn't reliable as the table won't be there
consistently, reason being GnuCash doesn't know about it when it creates
a db

More importantly, welcome to the GnuCash SQL solutions team.

did you look at
http://lists.gnucash.org/pipermail/gnucash-user/2014-December/057344.html
and decide it was crap?  recursive is the way to go these days unless
you're fighting a battle I'm not aware of.

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


also, I'm not sure it counts as a crosstab, but maybe I'm more
particular about terms than you.

Sebastien has made most progress so keep an eye out for his (rare these
days) posts and don't ever write to a GnuCash db unless you are very
sure you know what you are doing.

-- 
Wm


More information about the gnucash-user mailing list