Hi again, Bhavbhuti, no-one seems to want to answer your question, so
I'll answer this one as well...

Generally, Firebird isn't good at flattening data and except for LIST
(which gathers values into one comma separated blob field) or having a
stored procedure with dynamically builds a statement that you execute
through EXECUTE STATEMENT (which could execute the statement, but I
don't know how to return the values since output fields must be defined
when creating the stored procedure), I do not know of any way to flatten
data with an unknown number of columns.

A few times, I've had to produce a flattened dataset, and when trying to
do so using Firebird 1.5, it has required a lot of effort and I've run
into various problems (although generally succeeding in the end). What
I've found to be much less troublesome, is to export a data set and then
either use SPSS to flatten the data or use pivot tables in Excel. If you
use a report tool, I'd say that tool also ought to have options for
doing such flattening, but I've never tried to used report tools for
such a task.

What all this comes down to, is that I prefer for Firebird to get the
result set to the client, but let client tools do the formatting and
presentation - including flattening the data structure. If you have a
limited (and known) number of columns and rows to flatten, then it can
be done by Firebird, sometimes easy, sometimes with a bit more trouble,
but for returning an unknown number of fields, I think client programs
at least are better.

Thinking about it, if using the LIST function and trying to export data
into a .csv file, then when using the same character for separating the
values as you use to separate the fields, the only likely problem you
would get when importing the result set into another program would be to
name each column.

Set

Bhavbhuti Nathwani wrote:

> Dear all
>
> I have a many-side-table for taxation / addition / deduction records for invoices, let calls it Footer. This Footer table needs to be flattened for listing purposes like in a sales register. The simplified table structures, with data, involved are as follows:
>
> AccountsMasterTable
> iID, cName, cCode
> 1, ABC Ltd., null
> 2, Excise, CENVAT
> 3, Cash Discount, CD
> 4, Sales Tax, VAT
>
> InvoiceHeader
> iID, iBillNo, dBillDt, iPartyID, bAmt
> 101, 555, 2009.07.01, 1, 1000
> 102, 556, 2009.07.02, 1, 2000
>
> InvoiceFooter
> iID, iInvoiceID, iAccountID, nPerc, bAmt
> 201, 101, 2, 10, 100
> 202, 101, 4, 2, 20
> 203, 102, 2, 10, 200
> 204, 102, 3, 0, 25
>
> The iID fields are the PK for the respective tables. iPartyID and iAccountId fields are FK to AccountsMasterTable. iInvoiceId is the FK to InvoiceHeader table.
>
> The final layout for reporting purposes would be something like this:
> iBillNo, dBillDt, iPartyId, bAmt, cCode1, nPerc1, bAmt1, cCode2, nPerc2, bAmt2, cCode3, nPerc3, bAmt3, so.on...
> 555, 2009.07.01, 1, 1000, CENVAT, 10, 100, ST, 2, 20, CD, 0, 0
> 556, 2009.07.02, 1, 2000, CENVAT, 10, 200, ST, 0, 0, CD, 0, 25
>
> Is it possible to do it in pure SQL which allows for adaptability when Footer structure changes.
>
> Thanks and regards
> Bhavbhuti