SQL 如何将同一行的并列属性拆分到多行 *
有数据库表TBLORDERS，数据如下所示：
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
这个表实际很宽，这里我们仅取7列数据做为示例，需要将同一行的并列属性拆分到多行，结果如下：
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
需要在STEP_NUMBER中填入STEP[n]，并将STEP[n]_STATUS、STEP[n]_START、STEP[n]_END分别填入结果表对应列，同时计算STEP[n]_END与STEP[n]_START的日期差。
SQL Server的SQL大致如下：
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)
从自然思维上去解这道题并不难，除了第一列，后面有3n列，每3列对应一个步骤（step），只需要分n次，依次插入步骤次序（STEP[n]）、STEP[n]_STATUS、STEP[n]_START、STEP[n]_END、STEP[n]_END与STEP[n]_START的日期差即可。然而这类动态行列转换的问题在SQL中是出了名的难题，SQL中不允许将非常量表达式用于UNPIVOT值，所以没办法用UNPIVOT动态列转行，虽然SQL Server可以使用CROSS APPLY将左表的值依次插入右表，但因为本题的列数无法确定，要动态查出左表的数据也是极其困难的。
用开源集算器的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是专业的数据计算引擎，基于有序集合设计，提供了完善的集合运算，相当于Java和SQL优势的结合，很容易处理这样的动态行列转换的问题。
英文版