many to many query with 1 row per result?

SQL Apprentice Question
I have three tables
Student
-ID
-FirstName


Test
-ID
-Date



StudentTests
- TestID
- ChildID
- Grade


What I want to do is have a query that returns a student's information,
with a list of tests they've had. So, one row per student would be
ideal. I thought about changing table layout to have a fixed number of
tests, but I want to be able to change the number of tests pretty much
dynamically. Then I thought I could change table layout dynamically
(adding / removing columns as needed, and using dynamic SQL) but then I
thought that ... might not be the best idea :)


Right now I have this query
SELECT S.ID, S.FirstName, S.LastName, T.ID, T.Date, ST.Grade
FROM dbo.Student S
LEFT JOIN dbo.StudentTests ST ON ST.StudentID = S.ID
LEFT JOIN dbo.Test T ON T.ID = ST.TestID
ORDER BY S.LastName


Let's say there are 5 tests, I get 5 rows, I'm not super excited by
that, but I'm also not coming up with a way to change it.


So is there a way to do this?


Celko Answers
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.

You might also want to learn ISO-11179 data element naming rules so
that when you do write DDL, it will be usable. Only one student,
magical "id" in the tables, reserved words, compound table names (do
you really say "mid-term exams" or "mid-term studenttest"?), etc. This
is sloppy even for a personal pseudo-code. Is this what you meant?


CREATE TABLE Students
(student_id INTEGER NOT NULL PRIMARY KEY,
..);


CREATE TABLE TestForms
(test_id INTEGER NOT NULL PRIMARY KEY,
test_date DATETIME NOT NULL,
..);


CREATE TABLE Exams
(student_id INTEGER NOT NULL
REFERENCES Students(student_id)
ON UPDATE CASCADE,
test_id INTEGER NOT NULL
REFERENCES TestForms(test_id)
ON UPDATE CASCADE,
test_grade CHAR(1) DEFAULT 'I' NOT NULL
CHECK ( test_grade IN ('I', 'W', 'A', 'B', 'C', 'D', 'F'),
PRIMARY KEY (student_id, test_id));



>> What I want to do is have a query that returns a student's information, with a list of tests they've had. So, one row per student would be ideal. <<


Did you know that you are supposed to do reports in application and not
the database in a tiered architecture?


>> I thought about changing table layout to have a fixed number of tests, but I want to be able to change the number of tests pretty much dynamically. <<


Did you know that a table has a fixed number of columns by definition?
You are describing a report, which should be done in the front end.


>> Then I thought I could change table layout dynamically (adding / removing columns as needed, and using dynamic SQL) but then I thought that ... might not be the best idea :) <<


Finally, you got something right! Dynamic SQL is a way of saying that
you have no idea what to do, so you will let someone else decide at run
time.


>> Let's say there are 5 tests, I get 5 rows, I'm not super excited by that, but I'm also not coming up with a way to change it. <<


If the tests are attributes of an exam schedule, then each one gets a
column. just like height, weight and eye color would in a table that
models a person. If the tests are separate entities related to a
student, then each one gets a row in a gradebook or exams table.

This report is called a cross tabs and has been for the last 250+
years. So of course Microsoft calls it a PIVOT to be different. Her
is a quick way to write it in portable, standard SQL:


SELECT S1.student_id,
MAX(CASE WHEN T1.test_id = 1 THEN T1.test_grade ELSE '' END)
AS exam_1,
MAX(CASE WHEN T1.test_id = 2 THEN T1.test_grade ELSE '' END)
AS exam_2,
MAX(CASE WHEN T1.test_id = 3 THEN T1.test_grade ELSE '' END)
AS exam_3,
MAX(CASE WHEN T1.test_id = 4 THEN T1.test_grade ELSE '' END)
AS exam_4,
MAX(CASE WHEN T1.test_id = 5 THEN T1.test_grade ELSE '' END)
AS exam_5
FROM Students AS S1, Exams AS T1
WHERE S1.student_id = T1.student_id
GROUP BY S1.student_id;