SQL 2005 - Compress rows with nulls and duplicates into single rows

Question

I'm not sure exactly how to describe what I want to do, so I'll use a contrived example

On SQL Server 2005, Say I have a view with rows like this, call it vwGrades:

ID          AssnDate                AssnTxt       Sally       Ted         Bob
----------- ----------------------- ------------- ----------- ----------- -----------
2999        2007-09-22 00:00:00     Homework #1   20          NULL        NULL
2999        2007-09-22 00:00:00     Homework #1   NULL        0           NULL
2999        2007-09-22 00:00:00     Homework #1   NULL        NULL        24
2999        2007-09-22 00:00:00     Final Exam    57          NULL        NULL
2999        2007-09-22 00:00:00     Final Exam    NULL        0           NULL
2999        2007-09-22 00:00:00     Final Exam    NULL        NULL        35

How can I query it, such that I get this, ridding myself of all the annoying nulls and duplicate rows?

ID          AssnDate                AssnTxt       Sally       Ted         Bob
----------- ----------------------- ------------- ----------- ----------- -----------
2999        2007-09-22 00:00:00     Homework #1   20          0           24
2999        2007-09-22 00:00:00     Final Exam    57          0           35

Answer 1

Looks like a denormalized schema, you should have a FirstName column instead of Sally Ted and Bob. That would make the query much simpler. Can you refactor?

Answer 2

Select
   ID,
   AssnDate,
   AssnTxt,
   Max(IsNull(Sally,0)) AS Sally,
   Max(IsNull(Ted, 0)) As Ted,
   Max(IsNull(Bob, 0)) As Bob
From vwGrades
Group By
  ID,
  AssnDate,
  AssnTxt