从行添加自定义列

Question

我想编写一个查询，将行透视到具有自定义列名的列。

下面是我的示例输出:

Name Documents          Sent
---- ------------------ ----------
Paul Attachment1 - Paul 2020-01-01
Paul Attachment2 - Paul 2020-01-01
Ty   Attachment1 - Ty   2020-01-02

期望输出:

Name Attachment1        Attachment1Sent Attachment2        Attachment2Sent
---- ------------------ --------------- ------------------ ---------------
Paul Attachment1 - Paul 2020-01-01      Attachment2 - Paul 2020-01-01
Ty   Attachment1 - Ty   2020-01-02      NULL               NULL

文档的最大数量是两个，但最小的只有一个。

我正在使用SQL Server。我相信我可以使用PIVOT或CROSS APPLY来做到这一点，但我被困在如何创建自定义列名。任何帮助或建议都是感激的。谢谢你！

Answer 1

您可以使用条件聚合:

select name, 
max(case when seqnum = 1 then document end) as document_1,
max(case when seqnum = 1 then sent end) as sent_1,
max(case when seqnum = 2 then document end) as document_2,
max(case when seqnum = 2 then sent end) as sent_2
from (select t.*,
row_number() over (partition by name order by sent) as seqnum
from t
) t
group by name;