Oracle String concatenation

-- Three methods can be implemented:
-- Method 1: The version of wmsys. wm_concat Oracle must be 10 Gb or above.
-- Method 2: Use sys_connect_by_path for the Oracle version 9i and below
-- Method 3: Use User-Defined Functions

-- I will give you an example of UDF implementation.
-- View the employee name with Department 10 in the employee table

-- UDF implementation

Create or replace function my_concat (p_deptno in integer) return varchar2 -- remember: The parameter and the data type in the return value do not need to define the length is result varchar2 (4000); -- Define the variable, remember that the definition of variables in Oracle does not require begin for temp_cursor in (select ename from EMP where deptno = p_deptno) loop -- in this case, all employees in the corresponding department are queried in the cursor for loop: = Result | temp_cursor.ename | ','; end loop; Result: = rtrim (result, ','); -- remove the last space, return result; end;

 
Select distinct deptno, my_concat (deptno) name from EMP where deptno = 10;

Result:
Deptno name
-----------------------
30 Allen, Ward, Martin, Blake, Turner, James, black, Maxton