It looks like complicated variation on cross-tab report. I don't think it can be done with a static query. You can build a query for each set of three dynamicaly but it'll be more coding than creating reporting records.

>
>I have a many-side-table for taxation / addition / deduction records for invoices, let calls it InvoiceFooter. This InvoiceFooter 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
>205, 102, 4, 4, 80
>
>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, 4, 80, CD, 0, 25
>
>OR
>
>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, null, null, null
>556, 2009.07.02, 1, 2000, CENVAT, 10, 200, CD, 0, 25, ST, 4, 80
>
>Is it possible to do it in pure SQL which allows for adaptability, ie. variable column (in sets of threes) for as many applicable iAccountsID in the InvoiceFooter rows.
>
>I think a brute force can give the columns but columns will be hard-coded with CASEs or IIFs removing any chance of automatic adaptability.
>
>Please advise.