Convert Segmented Time Periods in SQL Using Cross Apply & Pivot

Question

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?

Answer 1

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

Answer 2

Data

CREATE TABLE mytable(
   ID            INTEGER  NOT NULL 
  ,PartNum       VARCHAR(40) NOT NULL
  ,EnteredOn     VARCHAR(40) NOT NULL
  ,PickedTime    VARCHAR(40)
  ,DeliveredTime VARCHAR(40)
);
INSERT INTO mytable(ID,PartNum,EnteredOn,PickedTime,DeliveredTime) VALUES 
(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);

Use unpivot and pivot together

select *
from 
(
 select CONCAT(PartNum,'_',ID) COL
  ,st,
  t_stamp
from (select ID,PartNum,
  EnteredOn as Entered,
  PickedTime as Picked,
  DeliveredTime as Delivered
  from mytable) m
unpivot
(
  t_stamp
  for st in ([Entered]
      ,[Picked]
      ,[Delivered]
      )
) unpiv
) src
pivot
(
  max(st)
  for COL in ([50A_100], [60B_125], [50A_171],[70B_211])
) piv;

you can use dynamic query as follows

DECLARE @SQL nvarchar(max)
DECLARE @cols nvarchar(max)
set @cols=(select string_agg(CONCAT('[',PartNum,'_',ID,']'),',') from mytable)
--print(@cols)

set @SQL='
  select *
from 
(
 select CONCAT(PartNum,''_'',ID) COL
  ,st,
  t_stamp
from (select ID,PartNum,
  EnteredOn as Entered,
  PickedTime as Picked,
  DeliveredTime as Delivered
  from mytable) m
unpivot
(
  t_stamp
  for st in ([Entered]
      ,[Picked]
      ,[Delivered]
      )
) unpiv
) src
pivot
(
  max(st)
  for COL in (' + @cols + ' )
) piv;

  '


--print @SQL
exec(@SQL)

dbfiddle

Answer 3

Something like this:

SELECT  t_stamp
,   max(CASE WHEN partnum = '50A' AND id = 100 THEN Status END) AS [50A_100]
,   max(CASE WHEN partnum = '60B' AND id = 125 THEN Status END) AS [60B_125]
,   max(CASE WHEN partnum = '50A' AND id = 171 THEN Status END) AS [50A_171]
,   max(CASE WHEN partnum = '70B' AND id = 211 THEN Status END) AS [70B_211]
FROM
(
    VALUES  (100, N'50A', N'2024-03-28 08:59:13.727', N'2024-03-28 09:30:20.237', N'2024-03-28 09:56:42.570')
    ,   (125, N'60B', N'2024-03-28 08:59:22.290', N'2024-03-28 09:31:32.543', N'2024-03-28 09:56:50.683')
    ,   (171, N'50A', N'2024-03-28 14:31:28.480', NULL, NULL)
    ,   (211, N'70B', N'2024-03-28 14:31:33.613', NULL, NULL)
) t (ID,PartNum,EnteredOn,PickedTime,DeliveredTime)
CROSS APPLY (VALUES (EnteredOn,'ENTERED')
                     ,(PickedTime,'PICKED')
                     ,(DeliveredTime,'DELIVERED')
             ) B(t_stamp,[Status])
WHERE   t_stamp IS NOT NULL
GROUP BY t_stamp

This is just a garden variety conditional aggregation where the MAX(CASE WHEN) creates columns for each part you're interested in.