Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004
    Posts
    35

    Unhappy Unanswered: Complex Query Problem

    HELLO ALL!
    Im in a bit of a pucker. I need to do something complex, but not really sure what the best aproach is. I need to do it w/ a combo of SQL and ASP.net/VB.net -- I am thinking I can just do it in SQL. But here is my problem.
    I have a table with Test Results in it that looks Something like this.

    pk | Test_desc | Score | Date_completed
    --------------------------------------------------
    1 | Test A | 79 | 1/2/2003
    2 | Test B | 76 | 1/2/2003
    3 | Test C | 87 | 1/2/2003
    4 | Test D | 90 | 1/2/2003
    5 | Test A | 79 | 1/3/2003
    6 | Test B | 44 | 1/3/2003
    7 | Test C | 99 | 1/3/2003
    8 | Test X | 100 | 1/3/2003
    9 | Test Y | 77 | 1/4/2003
    10 | Test Z | 78 | 1/4/2003

    They want to compair test scores, so I need my Results to look like this:

    Test_desc | 1/2/2003 | 1/3/2003 | 1/4/2003
    ----------------------------------------------------
    Test A | 79 |79 |
    Test B | 76 |44 |
    Test C | 87 |99 |
    Test D | 90 | |
    Test X | | 100 |
    Test Y | | | 77
    Test Z | | | 78


    My first thought is to create a temp tables, one for each date. However, I need it to be dynamic, because sometimes there could 3 dates, and sometimes there could be 15 or 67. Never really know. My thought is to some how do a group by and create a temp table for each date, and then do a join between the primary table (the first one I listed) with each of the subsidiary temp tables. Then only list the score column from my subsidiary temp tables. A temp table looking something like:

    test_des | score
    ---------------------------
    Test A | 79
    Test B | 76
    Test C | 87
    Test D | 90



    I don't know how to create a Dynamic temp table based on a group by, OR if this is even the best approach???? -- HELP PLEASE!!, even thoughts or bits and pieces would be greatly appreciated.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sometimes there could be 67????

    you will have to scroll horizontally no matter where/how you display this data

    you're looking for a crosstab report or pivot table

    both of those search terms (here at dbforums or in a search engine) will give lots more info
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2004
    Posts
    35

    Thanks

    Excellent! -- This looks to be very much what I want. Looks like I was trying to make it WAY more complicated. One question that still remains is what if the same test appears more than 1 time each day. Hopefully there is away to break that out as well.? -- Ill keep looking!. Thanks!

Posting Permissions

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