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

    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:

    ID ENTRY_ID SCHOOL
    ---------------------------
    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:
    SELECT a.school, b.school, c.school, d.school, e.school
    FROM table a,
    table b,
    table c,
    table d,
    table e
    WHERE a.id = b.id
    AND b.id = c.id
    AND c.id = d.id
    AND d.id = e.id
    AND a.school > b.school
    AND b.school > c.school
    AND c.school > d.school
    AND d.school > e.school

    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
    Location
    West Palm Beach, FL
    Posts
    2,713

    Wink

    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
  •