# * 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.