查询以获取Columns格式的多行数据-postgresql

Question

我有这样的表结构：table-contact_details

role                email        userId
Primary Contact    [e-mail]        1
Secondary Contact  [e-mail]        1
End User           [e-mail]        1
Primary Contact    [e-mail]        2
Secondary Contact  [e-mail]        2

结果数据应为：

Primary Contact       Secondary Contact    End User      UserId
[e-mail]                  [e-mail]          [e-mail]       1
[e-mail]                  [e-mail]          null           2

我无法将最终用户检索为"；空"；对于userId-2，如果所有三个角色的数据都存在，则会出现整行，或者如果任何角色丢失，则会丢失整行。

谁能建议一下这种方法吗？

--Postgres版本-12

Answer 1

您可以尝试使用此(条件聚合(

select
max(case when role = 'Primary Contact' then email else null end) as PrimaryContact,
max(case when role = 'Secondary Contact' then email else null end) as SecondaryContact,
max(case when role = 'End User' then email else null end) as EndUser,
userId    
from contact_details
group by userId