CODEWITHSUNDEEP
sqlsql-serversql-server-2005
Tristan Havelick
Tristan Havelick

Reputation: 69298

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

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

Upvotes: 1

Views: 2296

Answers (2)

Jeff
Jeff

Reputation: 5963

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

Upvotes: 6

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171421

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?

Upvotes: 0

Related Questions