Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2004

    Exclamation Unanswered: Beginner with crosstab queries

    I am having headaches trying to display some data in one table.
    An example of some of the contents from the table are below:

    1 10 Arizona
    1 20 Arizona State
    1 30 Texas
    2 10 Baylor
    2 20 Texas
    3 10 Colorado

    As you can see each ID has its own entry id, depending on how many school are assigned to that ID. The table has 1 to up to 10 entry_ids for each ID. The schools can be any of 70+ schools in the database, not just these that I listed.

    I would like to list each school in its own column like below:

    school1 school2 school3
    Arizona Arizona State Texas

    school1 school2 school3
    Baylor Texas null

    school1 school2 school3
    Colorado null null

    I already know ahead of time that the highest count of entry_id per id is 10 because of a Having query I ran. Will I need to create 10 separate Select statements for each scenario??

    This is what I have so far, but this is for ids with 5 entry_ids:
    FROM table a,
    table b,
    table c,
    table d,
    table e
    WHERE =
    AND =
    AND =
    AND =
    AND >
    AND >
    AND >
    AND >

    Unfortunately, it will not work for anything other than rows with 5 entry_ids per ID. I would hate to write out 10 different UNIONs if there is an easier way.

    Your help is much appreciated and I hope I have given enough detail for an answer.

  2. #2
    Join Date
    Jun 2003
    West Palm Beach, FL


    Try this link
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

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