oracle 列转行list,列转行函数-Focus on Oracle-51CTO博客

1、列转成行，如下数据 ID NAME ---------- ------------------------------ 10 ab 10 bc 10 cd 20 hi 20 ij 20 mn

6 rows selected

2、调用oracle的函数 SQL> select id,wmsys.wm_concat(name) name from idtable 2 group by id; ID NAME ---------- -------------------------------------------------------------------------------- 10 ab,bc,cd

20 hi,ij,mn

3、自定义 create or replace function getStrByList(fgf in varchar2,tab_name in varchar2,hzx in varchar2,hbwbx in varchar2,hzxcsz in varchar2) return string ----功能描述：oracle多行合并成一行 ----参数描述： ----fgf:分隔符，对组合形成的数据中间插入分隔符 ----tab_name：查询的数据源表 ----hzx:汇总项字段 ----hbwbx:合并文本项 ----hzxcsz:汇总项参数值 is sql_str varchar2(1024); result_val varchar2(1024); begin sql_str:='SELECT TRANSLATE (LTRIM (text,''@''),''*@'',''*'||fgf||''') researcherList FROM (SELECT ROW_NUMBER() OVER (PARTITION BY n_sec_code ORDER BY n_sec_code,lvl DESC) rn,n_sec_code, text FROM (SELECT n_sec_code, LEVEL lvl,SYS_CONNECT_BY_PATH (c_researcher_code,''@'') text FROM (SELECT '||hzx||' as n_sec_code, '||hbwbx||' as c_researcher_code,ROW_NUMBER() OVER(PARTITION BY '||hzx||' ORDER BY '||hzx||','||hbwbx||') x FROM '||tab_name||' where '||hzx||'='''||hzxcsz||''') a  CONNECT BY n_sec_code = PRIOR n_sec_code AND x - 1 = PRIOR x)) WHERE rn=1  ORDER BY n_sec_code'; EXECute IMMEDIATE sql_str INTO result_val; return result_val;

end getStrByList;

**********本博客所有内容均为原创，如有转载请注明作者和出处!!!**********

Name:    guoyJoe

QQ:        252803295

Email:    oracledba_cn@hotmail.com

OCM:    http://education.oracle.com/education/otn/YGuo.HTM

_____________________________________________________________

加群验证问题：哪些SGA结构是必需的，哪些是可选的？否则拒绝申请!!!

Oracle@Paradise 　总群:127149411

Oracle@Paradise No.1群:177089463(已满)

Oracle@Paradise No.2群:121341761

Oracle@Paradise No.3群:140856036