CODEWITHSUNDEEP
sqloracle-databasecastingoracle11gcollect
Rafey
Rafey

Reputation: 5

How should I proceed after running the collect command?

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production.

I have a table in the below format.

Number  User  Value
-------------------
1       A     25
1       B     28
2       C     30
2       D     35

This is what I want:

Number  User  Value
-------------------
1       A,B   25,28
2       C,D   30,35

I tried using a Listagg but it gives me ORA-01489: result of string concatenation is too long.

This was the listagg command: 

  SELECT "Number",
         LISTAGG ("user", ', ') WITHIN GROUP (ORDER BY "user") "user",
         LISTAGG ("value", ', ') WITHIN GROUP (ORDER BY "user") VALUE
    FROM table
GROUP BY "Number";

I cant create type as I dont have privileges. In one of the other similar questions, someone suggested using collect. So using this: 

  SELECT number, CAST (COLLECT (USER) AS SYS.DBMSOUTPUT_LINESARRAY)
    FROM emp
GROUP BY number;

I dont know where to go from here. When I run this query, I get this column: 

CAST(COLLECT(USER)ASSYS 
-----------------------
COLLECTION  
COLLECTION  
COLLECTION

As you can see my sql knowledge is very minimal. Any help would be much appreciated!

Upvotes: 0

Views: 609

Answers (1)

A.B.Cade
A.B.Cade

Reputation: 16915

Try using the xmlagg approach:

rtrim(xmlagg(xmlelement(e, name1 || ',')).extract('//text()').getclobval(), ',')

Here is a sqlfiddle demo

Upvotes: 1

Related Questions