How to arrange data according to date with PIVOT

Question

I really need help here as my brain can't function anymore for this. So I have these data as below:

PesID	CreDate	WeekOfMonth(CreDate)	TestingProgress
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%

For the WeekOfMonth (CreDate), the data is actually the concat of CreDate month and the week, so for 1st april to 3 april, it is considered as 5 week of month march according to calendar. So I need to change the WeekOfMonth(CreDate) into columns and arrange the data partition by PesID and order by CreDate. If the same PesID also have the same WeekOfMonth (CreDate), then I want to get the highest percentage of TestingProgress. The expected outcome should be as below :

PesID	3-1	3-2	3-3	3-4	3-5	4-1	4-2
Pes 1	NULL	NULL	NULL	NULL	50%	70%	100%
Pes 2	NULL	NULL	NULL	NULL	20%	NULL	50%
Pes 180	NULL	NULL	NULL	NULL	10%	NULL	NULL
Pes 3	NULL	NULL	NULL	NULL	25%	NULL	NULL
Pes 150	NULL	NULL	NULL	NULL	NULL	30%	NULL

However, what I currently have is something as below:

PesID	3-1	3-2	3-3	3-4	3-5	4-1	4-2
Pes 1	NULL	NULL	NULL	NULL	30%	70%	100%
Pes 150	NULL	NULL	NULL	NULL	NULL	30%	NULL
Pes 180	NULL	NULL	NULL	NULL	10%	NULL	NULL
Pes 2	NULL	NULL	NULL	NULL	20%	NULL	50%
Pes 3	NULL	NULL	NULL	NULL	25%	NULL	NULL

This outcome is arranged based on the PesID instead of the CreDate and I don't know how I should add ORDER BY clause with PIVOT. Below is my SQL code:

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

Can someone help me with this? Thank you very much.

user_1570000 · Answer

You must feed your pivot with just the essential columns. You may note I created a column MinDate which uses the window function min() over(), Then it is a small matter of Order By MinDate

Simplified version

Example

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

Returns

How to arrange data according to date with PIVOT

1 Answers