OraFAQ Forum: SQL & PL/SQL » How to display multiple records in a single row

Home » SQL & PL/SQL » SQL & PL/SQL » How to display multiple records in a single row (Oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to display multiple records in a single row [message #644826]

Wed, 18 November 2015 07:59

subash141
Messages: 2
Registered: November 2015

Junior Member

Hi All,

I am trying to display multiple row values in a single row.

I am trying with the below syntax ...

select deptno
, ltrim(sys_connect_by_path(ename,','))
from ( select deptno
, ename
, row_number() over (partition by deptno order by ename) -1 as seq
from emp )
where connect_by_isleaf = 1
connect by seq = prior seq +1 and deptno = prior deptno
start with seq = 1;

Expected output

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

But when i try with the above syntax i am getting "ora-01489 result of string concatenation is too long" error.

Please help me in this issue, its urgent.

Thanks,
Subash

Report message to a moderator

Re: How to display multiple records in a single row [message #644827 is a reply to message #644826]

Wed, 18 November 2015 08:01

cookiemonster
Messages: 13975
Registered: September 2008
Location: Rainy Manchester

Senior Member

Have you tried using listagg?

Report message to a moderator

Re: How to display multiple records in a single row [message #644828 is a reply to message #644826]

Wed, 18 November 2015 08:02

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Please help me in this issue, its urgent.
Please explain why it is urgent for us to solve this problem for you.

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

Report message to a moderator

Re: How to display multiple records in a single row [message #644830 is a reply to message #644826]

Wed, 18 November 2015 08:13

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

ora-01489 result of string concatenation is too long

ORA-01489: result of string concatenation is too long
 *Cause: String concatenation result is more than the maximum size.
 *Action: Make sure that the result is less than the maximum size.

SQL does not support VARCHAR2 result larger than 4000 bytes. Above this size you have to use CLOB.
Both SYS_CONNECT_BY_PATH and LISTAGG are limited to this size.
I posted an aggregate/analytical function to go above returning a CLOB here.

Report message to a moderator

Re: How to display multiple records in a single row [message #644832 is a reply to message #644826]

Wed, 18 November 2015 08:32

subash141
Messages: 2
Registered: November 2015

Junior Member

Hi,

I even tried with the below syntax also ...

select
deptno,listagg(ename, ',') within group (order by ename)
from emp
group by
deptno

With this also i am getting same error.

Thanks,
Subash

Report message to a moderator

Re: How to display multiple records in a single row [message #644833 is a reply to message #644832]

Wed, 18 November 2015 08:33

cookiemonster
Messages: 13975
Registered: September 2008
Location: Rainy Manchester

Senior Member

So look at Michel's solution

Report message to a moderator

Re: How to display multiple records in a single row [message #644848 is a reply to message #644833]

Wed, 18 November 2015 13:46

Solomon Yakobson
Messages: 3312
Registered: January 2010
Location: Connecticut, USA

Senior Member

I don't think there is need to create UDF STRAGG for CLOBS when biuilt-in XMLAGG does same and does it faster.

SY.

[Updated on: Wed, 18 November 2015 13:47]

Report message to a moderator

Re: How to display multiple records in a single row [message #644849 is a reply to message #644848]

Wed, 18 November 2015 14:04

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

But the STRAGG function can also be used in analytic form and in parallel mode.

Report message to a moderator

Previous Topic:	How to analyze complex sql queries
Next Topic:	extract string using regex

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jul 04 10:28:51 CDT 2026