[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now


URGENT HELP!!! creating crosstab views in firebird 1.5

Posted on 2004-08-07
Medium Priority
Last Modified: 2013-12-09
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
Question by:ZeldaDX
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
LVL 19

Accepted Solution

NickUpson earned 2000 total points
ID: 11745659
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)

Author Comment

ID: 11767434
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
LVL 19

Expert Comment

ID: 11767744
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question