Displaying multiple records in single row

If you want to display values of multiple records in a single row seperated by commas, it can be done using ‘SYS_CONNECT_BY_PATH’ or also using DBMS_UTILITY.TABLE_TO_COMMA procedure.

Example: If you want to display all employee names belonging to dept 10 seperated by comma in a single row… following are the samples

Sample code using SYS_CONNECT_BY_PATH

SELECT MAX (LTRIM (SYS_CONNECT_BY_PATH (ename, ','), ',')) emp_list
FROM (SELECT empno, ename, ROWNUM AS record_num
FROM emp
WHERE deptno = 10) e
START WITH record_num = 1
CONNECT BY record_num = PRIOR record_num + 1

Sample code using TABLE_TO_COMMA

DECLARE
l_tab DBMS_UTILITY.uncl_array;
l_out VARCHAR2 (4000);
l_index NUMBER;

CURSOR c_emp
IS
SELECT ename
FROM emp
WHERE deptno = 10;
BEGIN
l_index := 0;

FOR ename_rec IN c_emp
LOOP
l_index := l_index + 1;
l_tab (l_index) := ename_rec.ename;
END LOOP;

DBMS_UTILITY.table_to_comma (l_tab, l_index, l_out);
DBMS_OUTPUT.put_line (l_out);
END;
/

Reference for more details :
http://www.oratechinfo.co.uk/delimited_lists_to_collections.html

Advertisements
This entry was posted in PLSQL. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s