Join query with flattened result
I have the following entities
AddressType is simply an enum field that define if the Email is Personal/Work/Other.
Is it possible to do a query that returns a flattened result like the one in the following sample?
CustomerID Full Name Personal Email Work Email
----------- 开发者_运维知识库-------------- ----------------- -----------------------
1 John Doe johndoe@hotmail.com john.doe@company.com
select c.CustomerID,
c.FullName as [Full Name],
epersonal.AddressText as [Personal Email],
ework.AddressText as [Work Email]
from Customer c
left outer join Email epersonal on c.CustomerID = epersonal.CustomerID
and epersonal.AddressType = 'personal'
left outer join Email ework on c.CustomerID = ework.CustomerID
and epersonal.AddressType = 'work'
Two main choices:
1) Select it as typical (with two rows, one for each email), then use the pivot
operator to flatten.
Example of pivot
(I call it an example as I wrote it in notepad. It may be slightly wrong, but it should point you the right way):
select
CustomerID,
FullName
[1] as WorkEmail,
[2] as HomeEmail
from
(select
c.CustomerID, c.FullName, e.AddressText, e.AddressType
from
Customer c
join emails e on e.CustomerID = c.CustomerID) as Source
pivot (
AddressText
FOR AddressType in ([1], [2])
)
2) Join to the email table twice, once for each type of address. Suggest outer joins so if one is missing you still get the other.
精彩评论