How do I Create a Comma-Separated List using a PL/SQL Query?

[bharthi]

Hi experts,
I have T-SQL funtion, here the query is used to list the data separated by comma.how can we write in PL/SQL.

role
table for example: -------
audit
cement
mason
result should be: audit,cement,ma son.

ALTER FUNCTION dbo.fnGetRolesB yEmployeeID
(
@EmployeeID uniqueidentifie r
)
RETURNS varchar(max)
AS
BEGIN

DECLARE @Roles varchar(max)

SELECT @Roles = COALESCE(@Roles + ' , ', '') + R.Role
FROM Employees AS E LEFT OUTER JOIN
UsersInRoles AS U ON E.EmployeeID = U.UserID LEFT OUTER JOIN
Roles AS R ON U.RoleID = R.RoleID
WHERE (E.EmployeeID = @EmployeeID)

RETURN @Roles
END


I Converted in pl/SQL like this,
create or replace
FUNCTION fnGetRolesByEmp loyeeID
(
v_EmployeeID IN char
)
RETURN VARCHAR2
AS
v_Roles VARCHAR2(4000);

BEGIN
SELECT coalesce(v_Role s || ' , ', '') || R.ROLE

INTO v_Roles
FROM Employees E
LEFT JOIN UsersInRoles U
ON E.EmployeeID = U.UserID
LEFT JOIN Roles R
ON U.RoleID = R.RoleID
WHERE ( E.EmployeeID = v_EmployeeID ) ;
RETURN v_roles;
END;



but this query not fetches two values , could any one provide the solution, it would be appreciated.

[rajujrk]

Hi,

Try this

CREATE OR REPLACE 
FUNCTION fnGetRolesByEmployeeID
(
v_EmployeeID IN char
)
RETURN VARCHAR2
AS
v_roles_string VARCHAR2(4000);
BEGIN

WITH v_txns AS (
 [B]SELECT R.ROLE v_Roles
FROM Employees E
LEFT JOIN UsersInRoles U
ON E.EmployeeID = U.UserID
LEFT JOIN Roles R
ON U.RoleID = R.RoleID
WHERE ( E.EmployeeID = v_EmployeeID ) [/B]
 )
 SELECT MAX ([B]SYS_CONNECT_BY_PATH (v_Roles, ', ')[/B]) INTO v_roles_string
 FROM
  (SELECT v_roles_string, ROW_NUMBER () OVER (ORDER BY trans_id) AS curr
    FROM v_txns)
 CONNECT BY curr - 1 = PRIOR curr
 START WITH curr = 1;

 v_roles_string := SUBSTR (v_roles_string, 2);

RETURN v_roles_string;
END;

THanks
Rajkumar

[bharthi]

Hi ... i have one doubt in the above query can you please explain it.. WHAT IS THAT "v_txns".. i getting the error due to this "v_txns"... help me.. please . thanks again...

[rajujrk]

Hi,

See the below Query, v_txns is an Object that holds the records, No need to declare the v_txns

CREATE OR REPLACE 
FUNCTION fnGetRolesByEmployeeID
(
v_EmployeeID IN VARCHAR2
)
RETURN VARCHAR2
AS
v_roles_string VARCHAR2(4000);
v_Roles  VARCHAR2(4000);
BEGIN
 
WITH v_txns AS (
         SELECT R.ROLE v_Roles
            FROM Employees E
            LEFT JOIN UsersInRoles U
            ON E.EmployeeID = U.UserID
            LEFT JOIN Roles R
            ON U.RoleID = R.RoleID
            WHERE ( E.EmployeeID = v_EmployeeID ) 
 )
 SELECT MAX (SYS_CONNECT_BY_PATH (v_Roles, ', ')) INTO v_roles_string
 FROM
  (SELECT v_Roles, ROW_NUMBER () OVER (ORDER BY v_Roles) AS curr
    FROM v_txns)
 CONNECT BY curr - 1 = PRIOR curr
 START WITH curr = 1;
 
 v_roles_string := SUBSTR (v_roles_string, 2);
 
RETURN v_roles_string;
END;

select fnGetRolesByEmployeeID('614728') from dual;

OR

This is an another way to form a string separated with comma

SELECT distinct RTRIM(XMLAGG(XMLELEMENT(e, R.ROLE || ',')).EXTRACT('//text()'),',') Role
            FROM Employees E
            LEFT JOIN UsersInRoles U
            ON E.EmployeeID = U.UserID
            LEFT JOIN Roles R
            ON U.RoleID = R.RoleID
            WHERE ( E.EmployeeID = v_EmployeeID )

[rajujrk]

Hi

Are you trying this Query in ORACLE? OR other Databases like MS SQL SERVER or MYSQL?

Cause this Forum is for Oracle Database, and My Query will Work in Oracle DB

[bharthi]

Hi, rajujrk,
Your Code was awesome , thanks for your solution, Thanks , thanks a lot. :)