将具有null和重复项的行压缩为单行

小编典典

将具有null和重复项的行压缩为单行

sql

我不确定该如何描述自己想做的事，因此我将使用一个人为的示例

在SQL Server 2005上，说我有一个带有这样的行的视图，将其称为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

我如何查询它，以便得到此信息，使自己摆脱所有烦人的null和重复的行？

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

阅读 148

2021-03-17

共1个答案

小编典典

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

2021-03-17