本文介绍了一种使用SQL将单行记录转换为多行记录的方法，通过递归查询和临时表实现从一个销售订单号派生出多个带有不同覆盖期限的记录。此方法适用于需要将单一记录拆分为多个具有连续数值的场景。
SQL transformation
how can we convert single row to multiple row (example)
SAP_SALES_ORD_NUM COVERAGE_TERM
1122 3
I need result as
SAP_SALES_ORD_NUM COVERAGE_TERM
1122 1
1122 2
1122 3
with temp as(
select 1122 as SAP_SALES_ORD_NUM , 3 COVERAGE_TERM from sysibm.sysdummy1 ),
loops (SAP_SALES_ORD_NUM ,COVERAGE_TERM) as(
select SAP_SALES_ORD_NUM ,COVERAGE_TERM from temp
union all
select l.SAP_SALES_ORD_NUM ,case when l.COVERAGE_TERM > 1 then l.COVERAGE_TERM - 1 end as COVERAGE_TERM from loops l ,temp t where t.SAP_SALES_ORD_NUM = l.SAP_SALES_ORD_NUM and l.COVERAGE_TERM >=1
)
select * from loops where COVERAGE_TERM is not null SAP_SALES_ORD_NUM COVERAGE_TERM
----------------- -------------
1122 3
1122 2
1122 1
扫一扫
312
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
