开发者

Join query with flattened result

I have the following entities

Join query with flattened result

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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜