我希望对SQL表进行枢轴，但不确定表是否以我想要的方式枢轴。

Question

我有一个简单的sql表，它有四列数据类型

Athlete nvarchar(30)
Mydate  date
Mytime  varchar(8)
Session nvarchar(40)

Athlete   Mydate               Session       Mytime
Jerry     14/04/2009            200m         00:00:43
Jerry     14/04/2009            200m         00:00:44
Jerry     14/04/2009            200m         00:00:38
Jerry     14/04/2009            200m         00:00:40
Tom       14/04/2009            200m         00:00:45
Tom       14/04/2009            200m         00:00:48
Tom       14/04/2009            200m         00:00:40
Tom       14/04/2009            200m         00:00:47
Tom       14/04/2009            200m         00:00:48

现在我想要的是创建一个输出如下所示的查询

Athlete   Mydate       Session     Time1       Time2      Time3      Time4      Time5   Time6
Jerry     14/04/2009       200m   00:00:43    00:00:44   00:00:38   00:00:40
Tom       14/04/2009       200m   00:00:40    00:00:45   00:00:48   00:00:40      00:00:47

任何最受感谢的帮助

Answer 1

SQL表本质上是无序的，您似乎没有考虑到排序。尽管您可以使用pivot来解决这个问题，但我通常只是使用条件聚合来处理类似的问题。这里的关键是你需要一些时间上的数字。

with t as (
      select t.*,
             row_number() over (partition by athlete, mydate, session order by (select NULL)) as seqnum
      from sometable t
     )
select athlete, mydate, session,
       max(case when seqnum = 1 then time end) as time1,
       max(case when seqnum = 2 then time end) as time2,
       max(case when seqnum = 3 then time end) as time3,
       max(case when seqnum = 4 then time end) as time4,
       max(case when seqnum = 5 then time end) as time5
from t
group by athlete, mydate, session;