Oracle的列轉行函數

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:匯總項參數值

查看本欄目更多精彩內容：http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/

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;