Convert Segmented Time Periods in SQL Using Cross Apply & Pivot

My core table data looks like this. It is keeping track of order statuses.

ID	PartNum	EnteredOn	PickedTime	DeliveredTime
100	50A	2024-03-28 08:59:13.727	2024-03-28 09:30:20.237	2024-03-28 09:56:42.570
125	60B	2024-03-28 08:59:22.290	2024-03-28 09:31:32.543	2024-03-28 09:56:50.683
171	50A	2024-03-28 14:31:28.480	null	null
211	70B	2024-03-28 14:31:33.613	null	null

I need to write a query that converts it into this format:

t_stamp	50A_100	60B_125	50A_171	70B_211
2024-03-28 08:59:13.727	ENTERED	null	null	null
2024-03-28 08:59:22.290	null	ENTERED	null	null
2024-03-28 09:30:20.237	PICKED	null	null	null
2024-03-28 09:31:32.543	null	PICKED	null	null
2024-03-28 09:56:42.570	DELIVERED	null	null	null
2024-03-28 09:56:50.683	null	DELIVERED	null	null
2024-03-28 14:31:28.480	null	null	ENTERED	null
2024-03-28 14:31:33.613	null	null	null	ENTERED

In words, I need to create a t_stamp column that includes each timestamp from the original table. I need a column for each ID named [PartNum]_[ID]. I need to return ENTERED, PICKED, or DELIVERED based on the column the original timestamp comes from. In cases where the t_stamp does not correspond to any information from a given column, I need to simply return null. I understand that there will be numerous columns if there are numerous ID's in the source data, and a ton of nulls.

Here is as far as I got. I can get the data I need for each ID manually by putting that into the WHERE clause, but I don't know how to use PIVOT to get a column for each ID + PartNum combination. Would PIVOT even be the proper tool here?

Select B.* 
 From  MyTable A
 CROSS APPLY (VALUES (EnteredOn,'ENTERED')
                     ,(PickedTime,'PICKED')
                     ,(DeliveredTime,'DELIVERED')
             ) B(t_stamp,[Status])
WHERE ID = 100
ORDER BY t_stamp ASC

That query returns these results. How can I incorporate each ID (from original results above) into its own column and insert nulls as described above?

t_stamp	Status
2024-03-28 08:59:13.727	ENTERED
2024-03-28 09:30:20.237	PICKED
2024-03-28 09:56:42.570	DELIVERED

Edit: I should have clarified; the part numbers should not be hard coded into the query anywhere as we have a very high quantity of these, and the source data might include just 1 part number or it might include 15+. Is this still possible?

Solution

I suspect your columns need to be dynamic. If so, DYNAMIC SQL would be required, as you may know by now, SQL Server is declarative by design.

Example

Declare @SQL varchar(max) = (
Select string_agg(col,',') 
 From  (Select distinct id,Col = quotename(concat(PartNum,'_',ID)) 
         From YourTable
        )  A
)

Set @SQL = ' 
Select *
 From  (
         Select Item = concat(PartNum,''_'',ID)
               ,B.* 
          From  YourTable A
          CROSS APPLY (VALUES (EnteredOn,''ENTERED'')
                             ,(PickedTime,''PICKED'')
                             ,(DeliveredTime,''DELIVERED'')
                       ) B(t_stamp,[Status])
       ) src
 Pivot ( max(Status) for Item in ('+ @SQL +') ) pvt
 Where t_stamp is not null
'

Exec(@SQL)

Results