Hi everyone. I need help creating a query for a transportation company. I am trying to querying a table of rates that stores it's values vertically and want to display the information horizontally.

Below is an example of sample data from the table. One column in the table lists all the different cities that a group of breakpoints apply to; another lists the different weight breakpoints; and the third contains the corresponding rates for each weight break. See example below.

Weight Range
(not a field in the database
just added to describe meaning
of breakpoint column) City Breakpoint Rate
0 -100 A 100 $100
101 - 200 A 200 $200
201 - 300 A 300 $300
0 -100 B 100 $100
101 - 200 B 200 $200
201 - 300 B 300 $300


I want to display the information horizontally
City 0-100 101-200 201-300
A 100 200 300
B 100 200 300



The only other twist is that different companies have different weight breaks and they are all stored in the same table. For example Company ABC's rates have the following weight breaks.

Weight Range
(not actual field
in database) Breakpoint field Rate
0-100 --> 100 $100
101-200 --> 200 $200
201-300 --> 300 $300


Company XYZ can have breakpoints such as the following.
0-50 --> 100 $100
51-100 --> 200 $200
101-150 --> 300 $300

If creating one report to accomodate both companies is not possible then I could also create seperate reports for each company. Any help or suggestions would greatly be appreciated. Thanks in advance.

i would suggest that you do this in the application layer

I agree with r937. Don't make your SQL statement too complicated, because it will be a pain in the butt to fix or change things later. It would be way easier to loop through the results and place each value in a cell in a table. By doing this you can also do some error checking for bad data before you display.

Good luck
Hope it helps

This is essentially a "table pivoting" problem.
It's not clear from your example but I'm assuming that the number of output columns can be larger than 4, depending on the input data.
In that case, only recursive SQL (using "WITH", i.e., CTEs) will help you. (Or maybe your SQL engine has a built-in PIVOT functionality ...)

See http://tinyurl.com/6wugk for a related problem (with solution).

H.t.h.