表tTest:
col1,col2
1 A
2 B
1 B
2 C
1 C
2 D 按照col1分组,结果为:
col1,col2
1 A,B,C
2 B,C,D 在oracle中
这个SQL语句怎么写。
如果要构造函数把字段和表名都作参数,函数怎么写。
严重感谢
col1,col2
1 A
2 B
1 B
2 C
1 C
2 D 按照col1分组,结果为:
col1,col2
1 A,B,C
2 B,C,D 在oracle中
这个SQL语句怎么写。
如果要构造函数把字段和表名都作参数,函数怎么写。
严重感谢
SELECT 1123 ID,'2008-9-19' DAY,'9:7:47' TIME FROM DUAL UNION
SELECT 1123 ID,'2008-9-19' DAY,'19:7:47' TIME FROM DUAL UNION
SELECT 1123 ID,'2008-9-19' DAY,'13:7:47' TIME FROM DUAL UNION
SELECT 1123 ID,'2008-9-19' DAY,'12:7:47' TIME FROM DUAL UNION
SELECT 1123 ID,'2008-19-19' DAY,'12:7:47' TIME FROM DUAL
)
select ID,DAY,WMSYS.WM_CONCAT(TIME) TIME FROM A GROUP BY ID,DAY
select col1,wmsys.wm_concat(col2) from ttest
group by col1;
参考此贴,用sys_connect_by_path实现
http://topic.csdn.net/u/20080505/11/a0958b42-d938-465f-972a-0f61a2969c97.html?seed=491226048
max(case when rn = 2 then col2 end) || ',' ||
max(case when rn = 3 then col2 end) as col2
from (
select row_number() over(partition by col1 order by col1) as rn,
col1,
col2
from t
) x
group by col1;