sql server 2008 - Join query with flattened result -

i have following entities

addresstype enum field define if email personal/work/other.

is possible query returns flattened result 1 in following sample?

customerid    full name       personal email         work email -----------   --------------  -----------------      ----------------------- 1             john doe        johndoe@hotmail.com    john.doe@company.com 

two main choices:

1) select typical (with 2 rows, 1 each email), use pivot operator flatten.

example of pivot (i call example wrote in notepad. may wrong, should point right way):

select   customerid,   fullname   [1] workemail,   [2] homeemail   (select      c.customerid, c.fullname, e.addresstext, e.addresstype          customer c      join emails e on e.customerid = c.customerid) source pivot (   addresstext   addresstype in ([1], [2]) ) 

2) join email table twice, once each type of address. suggest outer joins if 1 missing still other.