Multi-line records to turn to a row with a comma-separated string

/ ** Multi-line records to a row to a comma-separated string * Usage: Oracle use * Usage case: select a.itemid, f_m_t (a.itemid) from ftstockAcount * Enter: Material ID * Output: Return to use All single numbers (repeated single numbers as a single number) describe: 1, using varchar2 (5000) as an intermediate amount, sufficient size is necessary. It doesn't take much space because it is growing, and Oracle is used, each record will call the function. After the function call is completed, the space occupied by VARCHAR2 (5000) is released. 2. Use the cursor every time, the database query will be performed, and the data record will be generated, which will cause the speed problem. the place: the cursor query modification can be used directly * author: Zhang Zhen Liang * / create or replace function F_M_T (INitemid in varchar2) return varchar2 is MergeStr varchar2 (5000); cursor cur is select distinct nvl (a.docno, '') baseValue from ftstockaccount a where a.itemid = INitemid; cur_row cur% ROWTYPE; begin MergeStr: = ''; - normal cursor / * open cur; loop fetch cur into cur_row; exit when cur% NOTFOUND; MergeStr: = MergeStr || ',' || Cur_row.basevalue; end loop; close cur_row in cur loop if mer Else MergeStr: = CUR_ROW.BASEVALUE; END IF; End Loop;

Return (MergeStr); END;