SQL transformation

最新推荐文章于 2024-09-26 10:39:47 发布

原创最新推荐文章于 2024-09-26 10:39:47 发布 · 674 阅读

0 ·

CC 4.0 BY-SA版权

DW 同时被 2 个专栏收录

43 篇文章

订阅专栏

database

18 篇文章

订阅专栏

本文介绍了一种使用SQL将单行记录转换为多行记录的方法，通过递归查询和临时表实现从一个销售订单号派生出多个带有不同覆盖期限的记录。此方法适用于需要将单一记录拆分为多个具有连续数值的场景。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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