* How to Split Attributes of Same Type in One Row and Convert Them into Multiple Rows in SQL?

We have a database table TBLORDERS as follows:

ORDERNUMBER

STEP1_STATUS

STEP1_START

STEP1_END

STEP2_STATUS

STEP2_START

STEP2_END

1

COMPLETE

2020-01-01

2020-02-01

INPROCESS

2020-01-15

2020-02-01

2

COMPLETE

2020-01-01

2020-02-01

INPROCESS

2020-01-15

2020-02-01

The actual table is rather wide and here we just use seven columns to do a computing a task. The goal is to split the same types of attributes in one row and put them into multiple rows. Desired result is as follows:

ORDERNUMBER

STEP_NUMBER

STEP_STATUS

STEP_START

STEP_END

DURATION_IN_DAYS

1

STEP1

COMPLETE

2020-01-01

2020-02-01

31

1

STEP2

INPROCESS

2020-01-15

2020-02-01

17

2

STEP1

COMPLETE

2020-01-01

2020-02-01

31

2

STEP2

INPROCESS

2020-01-15

2020-02-01

17

Under STEP_NUMBER, we enter values in the form of STEP[n], populate STEP[n]_STATUS values , STEP[n]_START values and STEP[n]_END values to the corresponding columns, and calculate the difference between STEP[n]_END and STEP[n]_START.

Below is SQL solution in SQL Server:

SELECT A.ORDER_NUMBER 

      ,B.*

      ,DURATION_IN_DAYS = DATEDIFF(DAY,B.STEP_START,B.STEP_END)

 FROM  TBLORDERS A

 CROSS APPLY ( VALUES ('STEP1',[STEP1_STATUS],[STEP1_START],[STEP1_END])

                     ,('STEP2',[STEP2_STATUS],[STEP2_START],[STEP2_END])

)B (STEP_NUMBER,STEP_STATUS,STEP_START,STEP_END)

 

This is not difficult if we approach it in a natural way of thinking. There are 3n columns after the first column. Every 3 columns correspond to one step, so we just need to insert the step number (STEP[n]), STEP[n]_STATUS, STEP[n]_START, STEP[n]_END, and the difference between the date of STEP[n]_END and that of STEP[n]_START. The dynamic column-to-row/row-to-column transpositions, however, are a long-lasting SQL difficulty. The language cannot use UNPIVOT to achieve the dynamic column-to-row transposition because it does not allow using the non-constant expression as UNPIVOT value. SQL Server offers CROSS APPLY to be able to insert value in the left table into the right table, but, in this case, it is extremely hard to query desired values from the left table as the number of columns cannot be predefined.

 

It is convenient to achieve the above algorithm with the open-source esProc SPL:

A

1

=connect("MSSQL").query@x("SELECT * FROM TBLORDERS")

2

=create(ORDERNUMBER,STEP_NUMBER,STEP_STATUS,STEP_START,STEP_END,DURATION_IN_DAYS)

3

=((A1.fno()-1)\3).("A2.insert(0,A1.ORDERNUMBER,\"STEP"/#/"\",STEP"/#/"_STATUS,STEP"/#/"_START,STEP"/#/"_END,

interval(STEP_START,STEP_END))").concat@c()

4

>A1.run(${A3})

5

return A2

 

SPL is the professional data computing engine. It is designed based on ordered sets, and offers a complete set of set-oriented operations by combining advantages of Java and SQL. It is really easy for it to handle dynamic row-to-column/column-to-row transpositions.

 

Q & A Collection

https://stackoverflow.com/questions/64094090/how-can-i-query-sql-server-table-of-events-and-dates-into-a-column-with-the-even