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