title: SQL - Multiple records into one line with conditions
body: <p>I have two tables that look like this</p>
  <p>Account Table</p>
  <pre><code>| Account Number | Account Name   |
  | -------------- | -------------- |
  | 12345          | Bob Jones      |
  | 12346          | Jenny Smith    |
  | 12347          | Mary Frederick |
  </code></pre>
  <p>Email Table</p>
  <pre><code>| Account Number         | Email Type    |Email                  | IsPrimary       |
  | ---------------------- | --------------|-----------------------|-----------------|
  | 12345                  | WORK          | BOBSMITH@STACK.COM    | 0               |  
  | 12345                  | HOME          | BOBSMITH@GMAIL.COM    | 0               |
  | 12345                  | WORK          | BOBSMITH@APPLE.COM    | 1               |
  | 12345                  | HOME          | BOBSMITH@HOTMAIL.COM  | 0               |
  | 12346                  | HOME          | JMSMITH@GMAIL.COM     | 1               |
  | 12346                  | WORK          | JMSMITH@DISNEY.COM    | 0               |
  | 12346                  | HOME          | JMSMITH@HOTMAIL.COM   | 0               |
  | 12347                  | HOME          | MFRED@HOTMAIL.COM     | 0               |
  </code></pre>
  <p>I need to end up with results like this:</p>
  <pre><code>| Account Number           | Account Name      | Home Email         | Work Email         | Alternate Email      |
  | ------------------------ | ----------------- |--------------------|--------------------|----------------------|
  | 12345                    | Bob Jones         | BOBSMITH@GMAIL.COM | BOBSMITH@APPLE.COM | BOBSMITH@HOTMAIL.COM |
  | 12346                    | Jenny Smith       | JMSMITH@GMAIL.COM  | JMSMITH@DISNEY.COM | JMSMITH@HOTMAIL.COM  |
  | 12347                    | Mary Frederick    | MFRED@HOTMAIL.COM  |                    |                      |
  </code></pre>
  <p>I have all possible combinations of emails such as multiple work and no home, no primaries at all, multiple primaries... etc.</p>
  <p>I need the logic to be:</p>
  <ul>
  <li>Home Email: When type is Home. If there is a primary, choose the first one. If there is no primary, choose the first non-primary home email. Otherwise make it blank</li>
  <li>Work Email: Same as above but for work</li>
  <li>Alternate Email: Any third email on the list. If there are more than 3, choose home over work.</li>
  </ul>
  <p>I have tried a ton of things and nothing has worked well. Here is my top attempt -two things don't work about it. The 'top 1' returns a lot of nulls and the code for alternates isn't always mutually exclusive to the home and work email, especially for people that have no primary checked:</p>
  <pre><code>select distinct
  A01.AccountNumber, 
  A01.FirstName,
  A01.LastName,
  (
      select top 1 
      case
         when (A07.Active = 1 and A07.EmailType = 'HOME' and A07.UseAsPrimary = 1)
          then A07.EmailAddress
         when (A07.Active = 1 and A07.EmailType = 'HOME')
          then A07.EmailAddress
      end 
      from A07_AccountEmails AS A07 
      where A07.AccountNumber = A01.AccountNumber
  
  ) as HomeEmail
  ,(
      select top 1 
      case
         when (A07.Active = 1 and A07.EmailType = 'WORK'  and A07.EmailType is not null)--and A07.UseAsPrimary = 1)
          then A07.EmailAddress
         when (A07.Active = 1 and A07.EmailType = 'WORK')
          then A07.EmailAddress
      end 
      from A07_AccountEmails AS A07 
      where A07.AccountNumber = A01.AccountNumber 
  
  ) as WorkEmail
  ,(
      select top 1 (A07.EmailAddress)
      from A07_AccountEmails A07
      where A07.AccountNumber = A01.AccountNumber
      and A07.Active = 1 and A07.UseAsPrimary = 0
  ) as AlternateEmailMulti
  from A01_AccountMaster A01
  left outer join A07_AccountEmails A07 on A07.AccountNumber = A01.AccountNumber
  where
  A01.[Status] = 'A'
  </code></pre>