将具有空值和重复项的行压缩为单行 [英] Compress rows with nulls and duplicates into single rows

问题描述

我不确定如何准确描述我想要做的事情，所以我会使用一个人为的例子

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

在 SQL Server 2005 上，假设我有一个像这样的行的视图，称之为 vwGrades:

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

推荐答案

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

这篇关于将具有空值和重复项的行压缩为单行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！