Need Query Syntax

The best way to ask my question is to present an example:

I have a view called List_Of_Active_Combined_Respondents_With_Counts
It has columns and sample data as follows:
Respondent_Email             Name                 Password         Resondent_Type         Count_Of_Things
john.smith@blah.com         Smith, John       AAAAAAAAAA              B                                    3
jane.doe@blah.com           Doe, Jane          BBBBBBBBBB              B                                    2
jane.doe@blah.com           Doe, Jane          CCCCCCCCC              T                                    7
bob.johnson@blah.com     Johnson, Bob     DDDDDDDDD              T                                    1

There are never more than 2 record for the same email address, and if there are 2 records, one will be Respondent_Type B and the other will be Respondent_Type T.

I am using the following SQL Syntax:
SELECT Respondent_Email,
      SUBSTRING(Respondent_Name, CHARINDEX(',',Name) + 2, 1000) AS First_Name,
      SUBSTRING(Respondent_Name, 1,CHARINDEX(',',Name) -1) AS Last_Name,
      Password,
      Respondent_Type,
      Count_Of_Things
FROM List_Of_Active_Combined_Respondents_With_Counts

The results I get are:
john.smith@blah.com         John    Smith       AAAAAAAAAA              B                                    3
jane.doe@blah.com           Jane     Doe         BBBBBBBBBB              B                                    2
jane.doe@blah.com           Jane     Doe         CCCCCCCCC              T                                    7
bob.johnson@blah.com     Bob      Johnson   DDDDDDDDD              T                                    1

What I want is to get this:
john.smith@blah.com         John    Smith       AAAAAAAAAA              B                                    3
jane.doe@blah.com           Jane     Doe         BBBBBBBBBB              C                                    9
bob.johnson@blah.com     Bob      Johnson   DDDDDDDDD              T                                    1

I want only one record per email address.  If that record represents a source record "doublet", then it should contain the email address, the first and last names, EITHER of the 2 passwords (I do not care which), a "C" to indicate the result was derived from a combination of B and T records in the source, and Count_Of_Things should be the combined values of the 2 source records.

All records that are singlets in the source, should come into the results just as they do now.

What should my SQL syntax be?Start Free Trial