CODEWITHSUNDEEP
sqlsql-servert-sqlpivot
Shin Yean
Shin Yean

Reputation: 43

How to arrange data according to date with PIVOT

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.

Upvotes: 4

Views: 41

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 81970

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

enter image description here

Upvotes: 3

Related Questions