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 `,?

Examples on how to use RowToCol Function:

Example 1: Where rows to be converted to a column string come from different table.

SELECT a.deptno, a.dname, a.loc,

rowtocol(‘SELECT DISTINCT job FROM emp WHERE deptno = ‘ ||a.deptno) as jobs

FROM dept a;

Example 2: Where the content in the WHERE clause is characters, put it in Sting format.

Notice, the main query and the passing query source is same table (EMP). So, use DISTINCT clause in the main query.

SELECT DISTINCT a.job

,rowtocol(‘SELECT ename FROM emp WHERE job = ‘ || ”” || a.job || ”” || ‘ ORDER BY ename’)

AS Employees

FROM emp a;

Code Listing:

CREATE OR REPLACE

FUNCTION rowtocol( p_slct IN VARCHAR2,

p_dlmtr IN VARCHAR2 DEFAULT ‘,’ ) RETURN VARCHAR2

AUTHID CURRENT_USER AS

/*

1) Column should be character type.
2) If it is non-character type, column has to be converted into character type.
3) If the returned rows should in a specified order, put that ORDER BY CLASS in the SELECT statement argument.
4) If the SQL statement happened to return duplicate values, and if you don’t want that to happen, put DISTINCT in the SELECT statement argument.

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;

/

How to transpose rows to columns in Oracle

Leave a Reply

Your e-mail address will not be published. Required fields are marked *

twenty eight − = twenty