Display Row Data as a Column

A HunBug Document
Document No.: 11310
Database Version: Oracle 9i +
Last Updated: 01 Oct 2006
Author: HunBug
It is often required to display data as a single column, especially data from child tables. This example looks using the basic function to return a formatted string method and then creating the extact same results by just using a more effiecient single query.

The examples below use the default scott schema tables dept and emp. These can be loaded by running the script
@?/rdbms/admin/scott.sql
The aim of both queries is to display a comma seperated list of employees by department.

Straight Forward Function
This is the straight forward example of creating a function to return a formatted string.
CREATE OR REPLACE FUNCTION dfn_namesByDept
( p_deptNo        IN NUMBER ) RETURN VARCHAR2 IS
  v_return        VARCHAR2(4000);
BEGIN
  FOR e IN ( SELECT eName
               FROM emp
              WHERE deptNo = p_deptNo
              ORDER BY eName )
  LOOP
    v_return := v_return||','||e.ename;
  END LOOP;
  RETURN(SUBSTR(v_return,2));
END;
/
We can then execute the following query which displays departments and the comma seperated list employees by using the above function.
set autotrace on

SELECT dName,
       scott.dfn_namesByDept(deptNo) employees
  FROM scott.dept;


DNAME           EMPLOYEES
--------------  -----------------------------------------
ACCOUNTING      CLARK,KING,MILLER
RESEARCH        ADAMS,FORD,JONES,SCOTT,SMITH
SALES           ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
OPERATIONS


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'DEPT'


Statistics
----------------------------------------------------------
         18  recursive calls
          0  db block gets
         16  consistent gets
          0  physical reads
          0  redo size
        594  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
          4  rows processed

Single SQL Statement
This example uses a single sql statement to produce the same result, using less resource and not requiring the need for objects to be created on the database to get the result.
set autotrace on

SELECT d.dName,
       e.employees
  FROM scott.dept d,
       ( SELECT x.deptNo,
                MAX(SUBSTR(SYS_CONNECT_BY_PATH(x.eName,','),2)) employees
           FROM ( SELECT deptNo,
                         eName,
                         ROW_NUMBER() OVER ( PARTITION BY deptNo
                                                 ORDER BY deptNo, eName ) rnum
                    FROM scott.emp ) x
          START WITH x.rnum = 1
        CONNECT BY x.rnum = PRIOR x.rnum + 1 AND PRIOR x.deptNo = x.deptNo
          GROUP BY x.deptNo ) e
 WHERE d.deptNo = e.deptNo(+);

 
DNAME           EMPLOYEES
--------------  -----------------------------------------
ACCOUNTING      CLARK,KING,MILLER
RESEARCH        ADAMS,FORD,JONES,SCOTT,SMITH
SALES           ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
OPERATIONS

 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   MERGE JOIN (OUTER)
   2    1     SORT (JOIN)
   3    2       TABLE ACCESS (FULL) OF 'DEPT'
   4    1     SORT (JOIN)
   5    4       VIEW
   6    5         SORT (GROUP BY)
   7    6           CONNECT BY (WITHOUT FILTERING)
   8    7             COUNT
   9    8               VIEW
  10    9                 WINDOW (SORT)
  11   10                   TABLE ACCESS (FULL) OF 'EMP'




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          6  consistent gets
          0  physical reads
          0  redo size
        594  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
          4  rows processed

Summary
As well as not requiring specific functions to work, the single sql query has better performance too. Although there may be a case for using the function method when complex formatting is required.

Further Reading


HunBug: Using Autotrace and Explain Plan
HunBug Store








Comments are all individually read, none are automatically posted to the site.