SELECT COL1,LTRIM(MAX(SYS_CONNECT_BY_PATH(COL2,',')),',') COL2
FROM
(
SELECT COL1,COL2,MIN(COL2) OVER(PARTITION BY COL1) COL2_MIN,
(ROW_NUMBER() OVER(ORDER BY COL1,COL2))+(DENSE_RANK() OVER (ORDER BY COL1)) NUMID
FROM T2
)
START WITH COL2=COL2_MIN
CONNECT BY PRIOR NUMID= NUMID-1
GROUP BY COL1;
如上图：我现在的表结构，该表来自于下面sql语句
select t1.userid userid, t2.projectname projectname
from ask_project_user t1, ask_project t2
where t1.projectid = t2.projectid
order by userid
我要转换成的结果如下图：
那么就可以套上面的公式，把COL1换成userid，COL2换成projectname
SELECT userid,
LTRIM(MAX(SYS_CONNECT_BY_PATH(projectname, ',')), ',') projectname
FROM (SELECT userid,
projectname,
MIN(projectname) OVER(PARTITION BY userid) COL2_MIN,
(ROW_NUMBER() OVER(ORDER BY userid, projectname)) +
(DENSE_RANK() OVER(ORDER BY userid)) NUMID
FROM (select t1.userid userid, t2.projectname projectname
from ask_project_user t1, ask_project t2
where t1.projectid = t2.projectid
order by userid) T2)
START WITH projectname = COL2_MIN
CONNECT BY PRIOR NUMID = NUMID - 1
GROUP BY userid;
这样就搞定了，如果想弄清原理，可以朝树结构方面想