DB2 ORACLE 递归查询 行转列

[b]ORACLE[/b]
递归查询：

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;

[b]DB2[/b]
递归查询：

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错误