My Oracle Support Banner

How To Retrieve Multiple Rows Into a Single Row? (Doc ID 367462.1)

Last updated on NOVEMBER 29, 2019

Applies to:

Oracle Database - Enterprise Edition - Version 9.2.0.5 and later
Information in this document applies to any platform.

Goal

NOTE: In the images and/or the document content below, the user information and environment data used represents fictitious data from the Oracle sample or bulit-in schema(s), Public Documentation delivered with an Oracle database product or other training material.  Any similarity to actual environments, actual persons, living or dead, is purely coincidental and not intended in any manner.

Is it possible to take the following SELECT and have the output return on singles lines as opposed to multiple lines?

For example, here is an expected and normal output.

SQL> SELECT DEPTNO, ENAME FROM EMP GROUP BY DEPTNO, ENAME;

DEPTNO ENAME
---------- ----------
10            KING
10            CLARK
10            MILLER
20            FORD
20            ADAMS
20            JONES
20            SCOTT
30           WARD
30           ALLEN
30           BLAKE
30           JAMES
30           MARTIN
30           TURNER

14 rows selected.


Here is the desired output - to have the Enames on a single line.

DEPTNO ENAME
---------- --------------------------------------------------
10 CLARK,KING,MILLER
20 JONES,SCOTT,ADAMS,FORD
30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES

Solution

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document
Goal
Solution


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.