I'm a noob at SQL. Sorry if my title isn't correct; Here's the problem:
I have a QODBC [1] query for QuickBooks that generates Profit & Loss report with dynamic columns:
sp_report
ProfitAndLossStandard
show
"Text",
"RowType",
"Amount" <-- dynamic
parameters
DateFrom= ?,
DateTo= ?,
SummarizeColumnsBy='Day'
With the parameter SummarizeColumnsBy='Day' you get x number of Amount columns for x number of days between DateFrom & DateTo (inclusive).
The above query with three days range looks something like this (first three rows):
| 0 | 1 | 2 | 3 | 4 | 5 | |
|---|---|---|---|---|---|---|
| 0 | Ordinary Income/Expense | TextRow | 366 | None | None | None |
| 1 | Income | TextRow | 366 | None | None | None |
| 2 | 4000 · Revenue | DataRow | 366 | [value] | [value] | [value] |
| ... |
Columns 3-5 are the Amount feature and they each show a summary value for a date in the range (only on row type == DataRow). (I also get this extra column, 2, not in the SQL request.)
I'm using Python and PyODBC to call QuickBooks. The resulting data is put into a Pandas DataFrame to represent the P&L in my script..
I can do some DataFrame carpentry to get the column names with Pandas, but is there a way to get the column names in the SQL syntax?
IE. 0 = "Text", 1 = "RowType", ...
[1]: In case you don't know, "QODBC is a fully functional ODBC driver for reading and writing QuickBooks ...accounting data files by using standard SQL commands."
