Subject Re: [firebird-support] Is it possible to have a SELECT with a variable number of columns?
Author Tupy... nambá
Post date 2013-11-18T15:22:16Z
So, give us news if you got resolved.

Roberto.


On Monday, November 18, 2013 10:18 AM, W O <sistemas2000profesional@...> wrote:


Thank you for the advice Roberto, I'll try

Greetings.

Walter.



On Mon, Nov 18, 2013 at 6:29 AM, Tupy... nambá <anhanguera@...> 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 <sistemas2000profesional@...> 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.