多行记录转换成一行，并用逗号隔开

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;

 

这样就搞定了，如果想弄清原理，可以朝树结构方面想

转载于:https://www.cnblogs.com/yichengbo/archive/2012/02/29/2373798.html