At 11:00 AM 21/08/2004 +0000, you wrote:

>Hi
>I have 2 tables, that look like:
>student(adminNo,fname,sname)
>results(adminNo,testNo,total,qofwc)
>
>where adminNo is the primary key.
>
>so i might have data in the student table that looks like:
>adminNo,fname,sname
>001,fred,smith
>002,emma,burt
>
>
>and in the results table:
>results:
>001,maths1,23,3
>001,maths2,30,2
>002,maths1,56,1
>002,maths2,25,4
>
>
>The students sit two tests. I need to create a report that looks
>like:
>
>
>student test1Score test2score avg Qofwc
>fred,smith 23 30 3
>emma,burt 56 25 3
>
>Basically the the report looks like an 'un-normalised' database but
>with qofwc being the average value of qofwc.
>
>Can anyone suggest how I should do this? Can it all be done with one
>SQL query, and if so what would it be?
>
>My delphi app is using quickrep components that come with delphi 6
>and IBObjects sql components.

What you're after here is a cross-tab set, a.k.a. "pivot query", so-called
(not very aptly) because ISAM database engines can spin the physical
structure of the table through 90 degrees and treat the columns as rows and
rows as columns.

You can get the "pivot" effect in relational databases using subqueries
(lots of em!). Your particular requirements are restricted to two and only
two tests, which isn't very flexible, but at least it simplifies the
problem. Here's how you could get the crosstab set in DSQL using a join and
a series of re-entrant correlated subqueries:

select
s.fname||',||s.sname as Student,
(select r2.Result from results r2
where r2.AdminNo = r1.AdminNo
and r2.TestID = 'maths1') as Test1Score,
(select r3.Result from results r3
where r3.AdminNo = r1.AdminNo
and r3.TestID = 'maths2') as Test2Score,
(select avg(QofWC) from Results r4
where r4.AdminNo = s.AdminNo
and ((r4.TestID = 'maths1') or (r4.TestID = 'maths2')) as AvgQofWC
from Results r1
join Student s
on r1.AdminNo = s.AdminNo
where r1.TestID = 'maths1' or r1.TestID = 'maths2'

All of this presupposes your tables are nice and tidy and the result
table's integrity is well-protected with constraints....it doesn't take
into account any possible need for outer joins (you have results that don't
match a student record, or you want output for all students, including
those for whom these particular results are not stored...)

It's easier and more flexible to create cross-tab sets in a selectable
stored procedure, though it's not necessarily a breeze to cover holes in
your database integrity. For (relative) simplicity, we'll assume that
there really are two sets of test results for every student we want to know
about, and that every pair of results stored belongs to a student that exists.

create procedure get_maths_results (Test1ID varchar(10), Test2ID varchar(10))
returns (
Student varchar(50),
Test1score integer,
Test2score integer,
AvgQofWC double precision)
as
declare AdminNo char(3);
declare OldAdminNo char(3) = 'xxx';
declare TestScore integer;
begin
/* init some vars */
Student = '';
AvQofWC = null;
/* start the loop to fetch the results */
for select
AdminNo,
TestID,
Result,
QofWC
from Results
where (TestID = :Test1ID or TestID = :Test2ID)
order by AdminNo
into :AdminNo, :TestID, :Result, :QofWC do
begin
if (AdminNo <> OldAdminNo and OldAdminNo <> 'xxx') then
/* output the old row and clear the decks */
begin
suspend;
OldAdminNo = AdminNo;
AvQofWC = null;
Student = '';
Test1Score = null;
Test1Score = null;
end
if (Student = '') then
select fname||','||sname from student
where AdminNo = :AdminNo
into :Student;
if (TestID = :Test1ID) then
Test1score = Result;
else
Test2score = Result;
if (AvQofWC is null) then
AvQofWC = 1.000 * QofWC;
else
AvQofWC = (AvQofWc + (QofWC * 1.000))/2;
end
end

./heLen