How to Split Attributes of Same Type in One Row and Convert Them into Multiple Rows
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:
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:
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.
Top comments (0)