Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2005
    Posts
    91

    Unanswered: Rate Table - Need Help To Display Data Horizontally

    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.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i would suggest that you do this in the application layer
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2006
    Location
    San Francisco, CA
    Posts
    136
    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

  4. #4
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    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.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •