Post reply

returning multiple records in one row

  • September 23, 2002 at 12:35 pm

    #78871

    Good afternoon,

    I know there is a way to do this, but I seem to be having a brain cramp. I have to create a mail merge document for the admissions office, to do this I will have to create one row of data for each student. The test score table has a row for each test. I want one row for each student with muliple tests. How would I write that?

    This is what I have that returns multiple rows:

    select p.people_code_id,p.last_name,p.first_name,

    (c.short_desc)Test,(tt.medium_desc)TestType,(t.alpha_score_1)Standard,(t.alpha_score_2)Percentile,(t.alpha_score_3)GradeEquiv,convert (varchar,t.test_date,101)TestDate

    from testscores t,people p, code_test c, code_testtype tt

    where p.people_code_id = t.people_code_id

    and c.code_value = t.test_id

    and tt.code_value = t.test_type

    Thanks in advance

  • September 23, 2002 at 1:57 pm

    #437136

    quote:

    Good afternoon,

    I know there is a way to do this, but I seem to be having a brain cramp. I have to create a mail merge document for the admissions office, to do this I will have to create one row of data for each student. The test score table has a row for each test. I want one row for each student with muliple tests. How would I write that?

    This is what I have that returns multiple rows:

    select p.people_code_id,p.last_name,p.first_name,

    (c.short_desc)Test,(tt.medium_desc)TestType,(t.alpha_score_1)Standard,(t.alpha_score_2)Percentile,(t.alpha_score_3)GradeEquiv,convert (varchar,t.test_date,101)TestDate

    from testscores t,people p, code_test c, code_testtype tt

    where p.people_code_id = t.people_code_id

    and c.code_value = t.test_id

    and tt.code_value = t.test_type

    Thanks in advance

    Gregory Larsen, DBA

    If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

    Gregory A. Larsen, MVP

  • September 23, 2002 at 2:00 pm

    #437137

    You could try to build a pivot table query. Check out my pivot table examples on the website listed below. Hope they give you some ideas.

    Gregory Larsen, DBA

    If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

    Gregory A. Larsen, MVP

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply