DB2 ORACLE 递归查询 行转列

薄7667

ORACLE
递归查询：

select parent_inst_id, inst_id
from uprr.u_base_inst
start with inst_id = '222000000000'
connect by prior inst_id = parent_inst_id;

行转列：

select user_id, wmsys.wm_concat(organ_id)
from v_organ_user_info
group by user_id;

DB2
递归查询：
WITH t(pid, id) AS(
SELECT parent_inst_id, inst_id
FROM uprr.u_base_inst
WHERE inst_id = '222000000000'
UNION ALL
SELECT c.parent_inst_id, c.inst_id
FROM t p, uprr.u_base_inst c
WHERE p.id = c.parent_inst_id)
SELECT * FROM t

行转列

WITH t1(u,o,num) AS (
SELECT user_id,organ_id,row_number() over(PARTITION BY user_id ORDER BY organ_id) FROM V_ORGAN_USER_INFO
),
t2(au,ao,anum) AS (
SELECT u,o,num FROM t1 WHERE num=1
UNION ALL
SELECT t1.u,t2.ao||','||t1.o,t1.num FROM t1,t2
WHERE t1.num=t2.anum+1 AND t1.u=t2.au AND t1.num <3)
SELECT au,ao FROM t2
WHERE aNUM = (SELECT max(anum) FROM t2 temp WHERE temp.au=t2.au)
ORDER BY t2.au;


其中DB2在行转列的时候，如果字段长度不够长，将会报22001错误