How to display different email address types in separate columns

Summary:

We have a requirement to display separate columns for work email address and home email address, one row of data per employee.

Content (required):

From reading other posts on the topic of separate columns, it has been recommended to use pivot tables however we would prefer to write this directly into the SQL or XML if possible. I can get data in separate columns using CASE formula however am still seeing two rows per person in the results.

Version (include the version you are using, if applicable):

21D

Code Snippet (add any code snippets that support your topic, if applicable):