So, give us news if you got resolved. Roberto.
On Monday, November 18, 2013 10:18 AM, W O <[email protected]> wrote: Thank you for the advice Roberto, I'll try Greetings. Walter. On Mon, Nov 18, 2013 at 6:29 AM, Tupy... nambá <[email protected]> wrote: > >Walter, > > >This possible using a cross-table. You can build it putting subqueries >side-side (in the result). > > >SELECT > a.Date, a.Invoice, a.Amount_Sale, > (SELECT FIRST 1 b.Amount_Tax FROM Table2 b WHERE b.IdTable1 = a.IdTable2) AS >Tax1, > (SELECT FIRST 2 SKIP1 b.Amount_Tax FROM Table2 b WHERE b.IdTable1 = >a.IdTable2) AS Tax2, > (SELECT FIRST 3 SKIP1 b.Amount_Tax FROM Table2 b WHERE b.IdTable1 = >a.IdTable2) AS Tax3, >/---- > > > other "columns" subqueries > > >----/ >FROM Table1 a > > >This will bring you something like a spreadsheet result, but you have to >stablish a limit of columns of taxes they may have. With a previous query, you >can ask for the maximum amount of taxes may exist in Table2 for one Table1 >row, and dinamically mount the above query. This can be a front-end SQL >generated code in Delphi, VB, a.s.o..(easy way) or in SQL itself, wich I think >can be a little harder to build, but possible. Try it by yourself. > > >Good luck, best regards, >Roberto Camargo, >Rio de Janeiro/Brazil. > > > >On Saturday, November 16, 2013 11:59 PM, W O ><[email protected]> wrote: > > > > >Hello everybody > > >I have an application where the tables are all normalized and so the taxes are >not in the same table as the sales. > > >Sometimes a sale should to pay 1 tax, sometimes 2 taxes, sometimes 3 taxes, >etc. > > >And it would be nice to have in just 1 row data of the sale and of the taxes, >each tax in its own column: > > >DATE, INVOICE, AMOUNT, TAX1, TAX2, TAX3, ... TAXN > > >Table1 >---------- >ID_TABLE1 >DATE >INVOICE >AMOUNT_SALE > > >Table 2 >---------- >ID_TABLE2 >ID_TABLE1 >ID_TAX >AMOUNT_TAX > > >Greetings. > > > >Walter. > > > > > >
