DEV Community

Cover image for Convert Cross Cell to Row Header, Row Header to Column — From SQL to SPL #4
Judith-Data-Processing-Hacks
Judith-Data-Processing-Hacks

Posted on

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.

source table

Task: 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.

expected results

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)
Enter fullscreen mode Exit fullscreen mode

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

SPL code script
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.

Top comments (1)

Collapse
 
judith677 profile image
Judith-Data-Processing-Hacks

Please make sure to follow our series if you are interested in leveraging your data processing skills! We have more hacks to share! 🌟✨