MS SQL 将数据转置到下一列，最大转置项目 = 5

Question

下面是我的数据格式

Name           Tel           Email
John           25671234      [e-mail]
Peter          98047766      [e-mail]
Peter          98047766      [e-mail]
Peter          98047766      [e-mail]
Peter          98047766      [e-mail]
Peter          98047766      [e-mail]
Peter          98047766      [e-mail]
Mary           33456789      [e-mail]

现在我想更改形式如下。如何使用 SQL Server 执行此操作？

Name           Tel           Email        Email1      EMAIL2      EMAIL3      EMAIL4     EMAIL5
John           25671234      [e-mail]
Peter          98047766      [e-mail]   [e-mail]  [e-mail]  [e-mail]  [e-mail] [e-mail]
Mary           33456789      [e-mail]

Answer 1

您可以枚举值并透视。 我会使用条件聚合：

select name, telephone,
max(case when seqnum = 1 then email end) as email_1,
max(case when seqnum = 2 then email end) as email_2,
max(case when seqnum = 3 then email end) as email_3,
max(case when seqnum = 4 then email end) as email_4,
max(case when seqnum = 5 then email end) as email_5
from (select t.*,
row_number() over (partition by name order by (select null)) as seqnum
from t
) t
group by name, telephone