sql server programming: Hello,

I'm a bit rusty with my Transact-SQL and I've come across an issue with a
Query.

I have the following pair of tables:

Scores Grants
-------- ---------------
scoreID grantID
judge_ID grantTitle
grant_ID
score

Sample Data:

Table Scores
scoreID judge_ID grant_ID score
-------- ---------- --------- -------
1 10 20 35
2 10 21 25
3 15 20 45
4 15 21 50

Table Grants
grantID grantTitle
-------- -----------
10 Title #1
20 Title #2

Result Table:

grantID grantTitle score1 score2 Sum Score
--------- ------------ --------- ---------- --------------
20 Title #2 25 50 75
21 Title #3 25 15 40

I'm stumped on how to extract the two judges scores and add the fields to
it's corresponding record based on Scores.grant_ID = Grants.grantID.

How would I accomplish this?

Thanks for any assistance with this.

[quoted text, click to view]

In SQL 2000:

SELECT s.GrantID, s.GrantTitle,
Score1 = SUM(CASE WHEN s.Rank = 1 THEN s.score ELSE 0 END,
Score2 = SUM(CASE WHEN s.Rank = 2 THEN s.score ELSE 0 END,
SumScore = SUM(s.score)
FROM (
SELECT g.GrantID, g.GrantTitle, s.score,
Rank = (SELECT COUNT(*) + 1
FROM Scores s2
WHERE s2.GrantID = s.GrantID
AND s2.JudgeID = s.JudgeID
AND s2.ScoreID < s.ScoreID )
FROM Grants g
INNER JOIN Scores s
ON s.GrantID = g.GrantID
) s
GROUP BY s.GrantID, s.GrantTitle

In SQL2005 you can use ROW_NUMBER() OVER() to calculate the Rank
column.

Chris

Correction:

SELECT s.GrantID, s.GrantTitle,
Score1 = SUM(CASE WHEN s.Rank = 1 THEN s.score ELSE 0 END,
Score2 = SUM(CASE WHEN s.Rank = 2 THEN s.score ELSE 0 END,
SumScore = SUM(s.score)
FROM (
SELECT g.GrantID, g.GrantTitle, s.score,
Rank = (SELECT COUNT(*) + 1
FROM Scores s2
WHERE s2.GrantID = s.GrantID
AND s2.JudgeID < s.JudgeID )
FROM Grants g
INNER JOIN Scores s
ON s.GrantID = g.GrantID
) s
GROUP BY s.GrantID, s.GrantTitle

Chris,

That worked perfectly. I was clueless how to tackle this problem. Thanks for
your assistance.

Albert

[quoted text, click to view]