Dear all

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.