SQL查询到具有相同客户端但具有不同地址代码的多个条目的组地址表

Question

我有一个表，地址详细信息如下。+表视图

-ClientID|Address Code|EMAIL
4566666|02|[e-mail]
4566666|03|[e-mail]
1566656|01|[e-mail]
1566656|02|[e-mail]
1566656|03|[e-mail]

预期结果：

ClientID|Address code 1|EMAIL 1| Address code 2| Email 2|Address code 3| Email 3|
4566666|02|[e-mail]|03|[e-mail]
1566656|01|[e-mail]|02|[e-mail]|03|[e-mail]

Answer 1

with cte as (
    select
        row_number() over(partition by "ClientID" order by "AddressCode") AS row_num,
        "ClientID",
        "AddressCode",
        "EMAIL"
    from Table1
)
select
    "ClientID",
    max(case when row_num = 1 then "AddressCode" end) as AddressCode1,
    max(case when row_num = 1 then "EMAIL" end) as Email1,
    max(case when row_num = 2 then "AddressCode" end) as AddressCode2,
    max(case when row_num = 2 then "EMAIL" end) as Email2,
    max(case when row_num = 3 then "AddressCode" end) as AddressCode3,
    max(case when row_num = 3 then "EMAIL" end) as Email3
from cte
group by "ClientID"

sql-fiddle演示