作者·2010-07-20 14:02

db2 由递归实现行转列。

字数 2200阅读 3856评论 4赞 0

工作中遇到一个要把行变列的问题，做如下记录。 表结构： NUM              CODE            CODE_DSCR ---------------- --------------- ---------------------- 0003083664       CV1             CV Test 1 0003083664       CV2             CV Test 2 0007011428       CV1             CV Test 1 0007020284       CV1             CV Test 1 需求： NUM              CODE_DSCR 
---------------- ---------------------- 0003083664       CV Test 1， CV Test 2 0007011428       CV Test 1 0007020284       CV Test 1 SQL：     with t1 as (         select row_number() over (partition by num order by code_dscr) as parent,            row_number() over (partition by num order by code_dscr)+1 as child,            num, code_dscr from grp_lookup     ),     t2 (parent, child, num, code_dscr, cnt) as (         select parent, child, num, code_dscr, 1 from t1         where parent=1         union all         select t2.child, t2.child+1, t2.num, rtrim(t2.code_dscr) || ', ' || rtrim(t1.code_dscr), cnt+1         from t2, t1         where t2.child=t1.parent         and cnt <=100         and t2.num = t1.num     )     select num, code_dscr from t2 where t2.child=(select max(child) from t1 where t2.num=t1.num)

著作权归作者所有

如果觉得我的文章对您有用，请点赞。您的支持将鼓励我继续创作！