From SQL to SPL：Convert cross cell to row header, row header to column

A table in the SQL Server database can be seen as a cross table, where the combination of the first two field values can be seen as a row header, and the last three field names can be seen as column headers, where the content and quantity of the row headers are uncertain.

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
211	70B	2024-03-28 14:31:33.613

Now we need to convert the table into a new cross table, where the original cross cells are converted into new row headers, the original row headers are converted into column headers, and the original column headers EnteredOn, PickedTime, and DeliveredTime are replaced with the strings ENTERED, PICKED, DELIVERED.

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

Dynamic SQL：

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)

Ordinary SQL has a pivot function for row column conversion, but column names must be written, which requires changing the code structure. First, use stored procedures or dynamic SQL to generate column names, and then spell out SQL. The code is very complex.

SPL code is much simpler and easier to understand: https://try.esproc.com/splx?3CA

	A
1	$select concat(PartNum,'_',ID) as PID, EnteredOn, PickedTime, DeliveredTime from tb.txt
2	=A1.pivot@r(PID;x,t_stamp;EnteredOn:"ENTERED",PickedTime:"PICKED",DeliveredTime:"DELIVERED")
3	=A2.select(t_stamp)
4	=A3.pivot(t_stamp;PID,x)

A1: Load data and concatenate the values of the first two fields.

A2, A4: Use pivot@r to convert columns to rows, and use pivot to convert rows to columns without writing column names.