sql - 如何使用 PIVOT 根据日期排列数据

Question

我真的需要帮助，因为我的大脑不能再为此工作了。所以我有这些数据如下:


识别号
创建日期
WeekOfMonth(CreDate)
测试进度


佩斯 1
30/03/2021
3-5
30%

佩斯 2
31/03/2021
3-5
20%

佩斯 180
02/04/2021
3-5
10%

佩斯 3
03/04/2021
3-5
25%

佩斯 1
03/04/2021
3-5
50%

佩斯 150
04/04/2021
4-1
30%

佩斯 1
06/04/2021
4-1
70%

佩斯 1
12/04/2021
4-2
100%

佩斯 2
15/04/2021
4-2
50%


对于 WeekOfMonth (CreDate)，数据实际上是 CreDate 月份和星期的连接，因此对于 4 月 1 日至 4 月 3 日，按日历视为月份的第 5 周。所以我需要将 WeekOfMonth(CreDate) 更改为列并按 PesID 排列数据分区并按 CreDate 排序。如果相同的 PesID 也具有相同的 WeekOfMonth (CreDate)，那么我希望获得最高百分比的 TestingProgress。预期结果应如下:


识别号
3-1
3-2
3-3
3-4
3-5
4-1
4-2


佩斯 1
空值
空值
空值
空值
50%
70%
100%

佩斯 2
空值
空值
空值
空值
20%
空值
50%

佩斯 180
空值
空值
空值
空值
10%
空值
空值

佩斯 3
空值
空值
空值
空值
25%
空值
空值

佩斯 150
空值
空值
空值
空值
空值
30%
空值


但是，我目前拥有的是以下内容:


识别号
3-1
3-2
3-3
3-4
3-5
4-1
4-2


佩斯 1
空值
空值
空值
空值
30%
70%
100%

佩斯 150
空值
空值
空值
空值
空值
30%
空值

佩斯 180
空值
空值
空值
空值
10%
空值
空值

佩斯 2
空值
空值
空值
空值
20%
空值
50%

佩斯 3
空值
空值
空值
空值
25%
空值
空值


这个结果是根据 PesID 而不是 CreDate 安排的，我不知道我应该如何用 PIVOT 添加 ORDER BY 子句。下面是我的 SQL 代码:

with
t1
as(
select [RN] = Row_Number()Over(Partition by [PesID],[WeekByMonth] Order by [CreDate] asc)
    ,*
from [MyTable]),

t2
as(select *
from t1
where [RN]='1')

select [PesID],[1-1],[1-2],[1-3],[1-4],[1-5],[2-1],[2-2],[2-3],[2-4],[2-5],[3-1],[3-2],[3-3],[3-4],[3-5],[4-1],[4-2],[4-3],[4-4],[4-5],[5-1],[5-2],[5-3],[5-4],[5-5],[6-1],[6-2],[6-3],[6-4],[6-5]
,[7-1],[7-2],[7-3],[7-4],[7-5],[8-1],[8-2],[8-3],[8-4],[8-5],[9-1],[9-2],[9-3],[9-4],[9-5],[10-1],[10-2],[10-3],[10-4],[10-5]
,[11-1],[11-2],[11-3],[11-4],[11-5],[12-1],[12-2],[12-3],[12-4],[12-5]
from
( 
    select [PesID],[TestingProgress],[WeekByMonth]
    from t2

) d

pivot
(
    min(TestingProgress)
     for [WeekByMonth] in ([1-1],[1-2],[1-3],[1-4],[1-5],[2-1],[2-2],[2-3],[2-4],[2-5],[3-1],[3-2],[3-3],[3-4],[3-5],[4-1],[4-2],[4-3],[4-4],[4-5],[5-1],[5-2],[5-3],[5-4],[5-5],[6-1],[6-2],[6-3],[6-4],[6-5]
,[7-1],[7-2],[7-3],[7-4],[7-5],[8-1],[8-2],[8-3],[8-4],[8-5],[9-1],[9-2],[9-3],[9-4],[9-5],[10-1],[10-2],[10-3],[10-4],[10-5]
,[11-1],[11-2],[11-3],[11-4],[11-5],[12-1],[12-2],[12-3],[12-4],[12-5])
) piv

有人可以帮我弄这个吗？非常感谢。

Answer 1

您必须仅使用基本列来提供枢轴。你可能注意到我创建了一个专栏 MinDate它使用窗口函数 min() over() ，那么就是Order By MinDate的小事了
简化版
示例

Set DateFormat DMY

Declare @YourTable Table ([PesID] varchar(50),[CreDate] date,[WeekOfMonth(CreDate)] varchar(50),[TestingProgress] varchar(50))  Insert Into @YourTable Values 
 ('Pes 1','30/03/2021','3-5','30%')
,('Pes 2','31/03/2021','3-5','20%')
,('Pes 180','02/04/2021','3-5','10%')
,('Pes 3','03/04/2021','3-5','25%')
,('Pes 1','03/04/2021','3-5','50%')
,('Pes 150','04/04/2021','4-1','30%')
,('Pes 1','06/04/2021','4-1','70%')
,('Pes 1','12/04/2021','4-2','100%')
,('Pes 2','15/04/2021','4-2','50%')

Select [PesID],[3-4],[3-5],[4-1],[4-2],[4-3]
  From (
        Select MinDate = min(CreDate) over (partition by [PesID])
              ,[PesID]
              ,[WeekOfMonth(CreDate)]
              ,[TestingProgress]
         From  @YourTable
       ) src
 Pivot (max([TestingProgress]) for [WeekOfMonth(CreDate)] in ( [3-4],[3-5],[4-1],[4-2],[4-3] ) ) pvt
 Order By MinDate

返回