Convert Cross Cell to Row Header, Row Header to Column — From SQL to SPL #4

Problem description & analysis:

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.

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.

Code Comparisons:

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:

SPL code is much simpler and easier to understand: try.DEMO

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.

---

Download open-sourced esProc SPL and see how SPL streamlines the process: Open-Source Address.