两列的 SQL 透视会导致所有透视列中出现空值

Question

我正在尝试创建这个：

Last Name, First Name, Email1, Email2, Email3, Phone1, Phone2, Phone3

从此查询：

;with CTE AS (
SELECT   FirstName, LastName, Email, Phone, Zip
FROM   Mother.dbo.People
Join   Mother.dbo.PeopleEmail on People.PeopleID = PeopleEmail.PeopleEmailID
Join   Mother.dbo.PeoplePhone on People.PeopleID = PeoplePhone.PeopleID
Where Zip IN ('34201','34202','34203','34205','34207','34208')
) Select * from CTE
PIVOT
(
MAX(Email)
FOR Email IN (Email1, Email2, Email3)
) AS E
PIVOT
(
MAX(Phone)
For Phone IN (Phone1, Phone2, Phone3)
) AS P;

但是，看起来我的所有电子邮件和电话字段都返回为空。

郑重声明，

1. 我正在使用 SQL Server 2017
"人员">
2. 表具有"名字"和"姓氏"，对于"人员电子邮件"("电子邮件"字段(和"人员电话"("电话"字段(为一对多。

我错过了什么？ 谢谢！

Answer 1

你最好使用条件聚合。 但是您需要枚举值才能透视它们：

select p.FirstName, p.LastName, Email, Phone, p.Zip
from Mother.dbo.People p join
(select PeopleId,
max(case when seqnum = 1 then email end) as email1,
max(case when seqnum = 2 then email end) as email2,
max(case when seqnum = 3 then phone end) as email3,
max(case when seqnum = 1 then phone end) as phone1,
max(case when seqnum = 2 then phone end) as phone2,
max(case when seqnum = 3 then email end) as phone3
from ((select pe.PeopleID, pe.email, null as phone,
row_number() over (partition by pe.PeopleID order by pe.PeopleEmailID) as seqnum
from Mother.dbo.PeopleEmail pe
) union all
(select pp.PeopleID, null as email, pp.phone,
row_number() over (partition by pp.PeopleID order by pp.PeoplePhoneId) as seqnum
from Mother.dbo.PeopleEmail pe
)
) em
group by PeopleId
) em
on p.PeopleID = em.PeopleEmailID
Where p.Zip IN ('34201', '34202', '34203', '34205', '34207', '34208');

编辑：

或者你可以把它表述为：

select p.FirstName, p.LastName, Email, Phone, p.Zip
max(case when seqnum = 1 then email end) as email1,
max(case when seqnum = 2 then email end) as email2,
max(case when seqnum = 3 then phone end) as email3,
max(case when seqnum = 1 then phone end) as phone1,
max(case when seqnum = 2 then phone end) as phone2,
max(case when seqnum = 3 then email end) as phone3
from ((select pe.PeopleID, pe.email, null as phone,
row_number() over (partition by pe.PeopleID order by pe.PeopleEmailID) as seqnum
from Mother.dbo.PeopleEmail pe
) union all
(select pp.PeopleID, null as email, pp.phone,
row_number() over (partition by pp.PeopleID order by pp.PeoplePhoneId) as seqnum
from Mother.dbo.PeopleEmail pe
)
) em join
Mother.dbo.People p
on p.PeopleID = em.PeopleEmailID
where p.Zip in ('34201', '34202', '34203', '34205', '34207', '34208');