Joining to a vertical table (but want data to come back horizontally)

[HoosHot]

Hi, I have 3 tables:

Trans_tbl
xid data1 data2
1 abc def
1 ghi klm
2 nop qrs

Amount_tbl
xid amt1 amt2
1 10 20
2 30 40

Summary_tbl
xid tier mins fee
1 1 14 1
1 2 15 7
1 3 12 9
2 1 9 4
2 2 7 3


Prior to adding Summary_tbl to my report, I simply joined Trans_tbl and Amount_tbl and would correctly get 3 rows in my report:
xid data1 data2 amt1 amt2
1 abc def 10 20
1 ghi klm 10 20
2 nop qrs 30 40

Now, I'm trying to add Summary_tbl and finding it very hard to get 3 rows. Ideally, I'd like to have this:
xid data1 data2 amt1 amt2 tier1 mins1 fee1 tier2 mins2 fee2 tier3 mins3 fee3
1 abc def 10 20 1 14 1 2 15 7 3 12 9
1 ghi klm 10 20 1 14 1 2 15 7 3 12 9
2 nop qrs 30 40 1 9 4 2 7 3 null null null

If I knew that there would be a max of 3 tiers in Summary_tbl, then I could join Summary_tbl 3 times to the existing Trans_tbl and Amount_tbl, but 3 is not a magic number. There could be 3, there could be 1, there could be 10 tiers - it varies.

Is there a way I can dynamically join an unknown number of times of the Summary_tbl? A horizontal view of a Summary_tbl would work, but I still have the same problem - basically, I can't (or at least I don't know how to) turn a table on its side when the number of final colums (or initial rows) is variable.

Thanks for any help!

 

[FVTrainer]

Ok, so the number of tiers varies but is there a maximum number of possible tiers? And what is your backend database (well, the real question is does it support stored procedures and could you use one as the basis for your report?)

 

[HoosHot]

there is not a maximum number of possible tiers (hence, the vertical table as opposed to a horizontal table).

database is oracle 8i so yes, it supports stored procedures. i've never used stored procedures as the basis of my crystal reports - i've only used simple table joins and selects.

thanks.

 

[FVTrainer]

I'm sorry to only be asking questions, but how do want to display the data once you have it? If you can have an unlimited amount of tiers and (ideally) a 1:1 relationship between xid's in all tables, how can you display that data in the report? In other words, if you had 20 tiers for a given xid, how would you show it on the report? Doesn't it make more sense to show the data vertically, in which case you wouldn't need the "ideal" table you've described?