The objective of this function is to transpose rows to columns.
This RowToCol function is built with invoker-rights AUTHID CURRENT_USER. The function works on tables/views of the user who invokes the function, not on the owner of this function.
RowToCol takes two parameters:
1. SQL Statement: You can pass any valid SQL statement to this function.
2. Delimiter: You can pass any character as a delimiter. Default value is `,?
CREATE OR REPLACE FUNCTION ROWTOCOL(P_SLCT IN VARCHAR2,
P_DLMTR IN VARCHAR2 DEFAULT ',') RETURN VARCHAR2
AUTHID CURRENT_USER AS
TYPE C_REFCUR IS REF CURSOR;
LC_STR VARCHAR2(4000);
LC_COLVAL VARCHAR2(4000);
C_DUMMY C_REFCUR;
L NUMBER;
BEGIN
OPEN C_DUMMY FOR P_SLCT;
LOOP
FETCH C_DUMMY
INTO LC_COLVAL;
EXIT WHEN C_DUMMY%NOTFOUND;
LC_STR := LC_STR || P_DLMTR || LC_COLVAL;
END LOOP;
CLOSE C_DUMMY;
RETURN SUBSTR(LC_STR, 2);
EXCEPTION
WHEN OTHERS THEN
LC_STR := SQLERRM;
IF C_DUMMY%ISOPEN THEN
CLOSE C_DUMMY;
END IF;
RETURN LC_STR;
END;
具体使用 方法 ：
(1)
SELECT A.DEPTNO,
A.DNAME,
A.LOC,
ROWTOCOL('SELECT DISTINCT job FROM emp WHERE deptno = ' || A.DEPTNO) AS JOBS
FROM DEPT A;
(2)
SELECT DISTINCT A.JOB,
ROWTOCOL('SELECT ename FROM emp WHERE job = ' || '''' ||
A.JOB || '''' || ' ORDER BY ename') AS EMPLOYEES
FROM EMP A;
(3)
SELECT DISTINCT customer_id, rowtocol ('SELECT FEE_TYPE_NO
FROM CUSTOMER_FEE_MONTH T where CUSTOMER_ID='
||customer_id)
FROM CUSTOMER_FEE_MONTH
WHERE CUSTOMER_ID = '0000011996'
AND year_month='200702' ;
(4)
多个条件加入
SELECT DISTINCT A.year_month,A.customer_id, rowtocol ('SELECT FEE
FROM CUSTOMER_FEE_MONTH T where CUSTOMER_ID ='||'''0000011996'''||'
AND fee_type_no='||'''153'''||'and CUSTOMER_ID='
||A.customer_id || 'and year_month='||A.YEAR_MONTH)
FROM CUSTOMER_FEE_MONTH A
WHERE CUSTOMER_ID = '0000011996'
