URGENT HELP!!! creating crosstab views in firebird 1.5

Hello guys:

I have 3 tables the first one, for example, this is a routes table:

ID      Name       Description
1       Route1   First destination
2       Route2   Second destination
3       Route3   thirth destination
4       Route4   fourth destination

I have another table called Products
ID      Name       Description
A       Product1   First product
B       Product2   Second Product
C       Product3   thirth Product
D       Product4   fourth Product

and a detail table:

A                        1                12
B                        2                56
C                        3                89
D                        4               109          

And i want to print a report with this information:

Product name          Route1      Route2     Route3      Route4........RouteN<----Routes table rows
Product1                     1               0              0             0                 N
Product2                     0               2              0             0                 M
Product3                     0               0              3             0                 O
Product4                     0               0              0             4                 P
.                                 .                .               .             .                  .
.                                 .                .               .             .                  .
.                                 .                .               .             .                  .
ProductM                    A               B              C             D                 Z

the problem is that i don't know how to put a dinamic columns in interbase...I'm using delphi 5, firebird 1.5 and reportbuilder... I was trying several ways to make it but i haven't the result i want yet... can somebody help me?....I don't care if i use stored procedures, triggers,delphi code or whatever you think. I only want to make a view to print the report i mentioned above...

                                                             Thanks a lot

                                                                Zelda DX
Who is Participating?
Nick UpsonPrincipal Operations EngineerCommented:
You can't do this in the way you describe, the SQL statement needs to know how many columns
to return and it can't until it retreived them.

The best Way I've found to do this is to do this select

Select r.id, p.id, d.qty
from routes r, products p, detail d
where r.id = d.routeid and p.id = d.productid

into a non-visible grid, then you can construct your final result table in the client ( I think, I'm no expect on Delphi)
ZeldaDXAuthor Commented:
Hello NickUpson

The problem with that sentence is that i cannot retrieve the values i want. My target is reduce the use of paper. I can solve that situation with this:

select p.name,r.name,d.qty
from routes r, products p,detail d
where r.id=d.routeid and p.id=d.productid
group by p.name,r.name,d.qty

And make a report with this format:
        Route1    1
        Route2    0
        Route3    0

   The problem is that i have to print this info in a half page. I need to print at least 15 products in one report and print 2 reports in one letter size page. With this option i can print only 2 or 3 products per page (if the route doesnt increment). My goal is use the less of paper sheets as posible.

                                                                       Thanks a lot
Nick UpsonPrincipal Operations EngineerCommented:
you will only get a row where a joining row exists on the detail table, the SQL won't work quite like that anyway.

select p.name,r.name, sum(d.qty) as XYZ
from routes r, products p,detail d
where r.id=d.routeid and p.id=d.productid
group by p.name,r.name

will give
product  route     XYZ
product1 route1  12
rouduct2 route2  56

the XYZ column will only contain zero if there is a row in the detail table with a qty of zero
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.