The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments, but we might still sneak in a few Office Hours sessions! Thanks for being a member of the AskTOM community.

Thanks for the question, Arun.

Asked: May 19, 2000 - 6:16 am UTC

Last updated: November 01, 2010 - 3:23 pm UTC

Version: 8i

Viewed 50K+ times! This question is

You Asked

Hi,

Is there some way to concatenate the output of a select query to a single record.

for ex. in SQL Anywhere there is a function LIST

select list(tname) from tab;

would give a comma seprate list of all the values in tname column.

is there something quivalent in oracle or some way around to do it.

and Tom said...

there are a couple of ways to do this in Oracle.

If you know the range of values for the column you wish to "list", you should use DECODE(). An example of using decode to do this is at:

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:124812348063 <code>

Now, I will show 2 more ways to do it by calling PLSQL from sql. The first example uses static sql and hence you would need to code a routine per column/table you wished to "list" on. The second, more generic example, may be used on any table/column pair as it uses dynamic sql:

First we will set up a demo table. We have 2 columns A and B. We desire output in the format:

A-value1 b-value1 b-value2 b-value3 ....
A-value2 b-value1 b-value2 ....

ask_tom@OSI1.WORLD> column t format a30
ask_tom@OSI1.WORLD> drop table t;
Table dropped.

ask_tom@OSI1.WORLD> create table t
2 ( a varchar2(25),
3 b varchar2(25)
4 );

Table created.

ask_tom@OSI1.WORLD> insert into t values ( '210','5000' );
ask_tom@OSI1.WORLD> insert into t values ( '210','5001' );
ask_tom@OSI1.WORLD> insert into t values ( '210','5002' );
ask_tom@OSI1.WORLD> insert into t values ( '220','6001' );
ask_tom@OSI1.WORLD> insert into t values ( '220','6002' );
ask_tom@OSI1.WORLD> commit;

Commit complete.

Our first implementation simply uses static sql to select all of the values for B from T for a given A and string them together:

ask_tom@OSI1.WORLD> create or replace
2 function get_transposed( p_a in varchar2 )
3 return varchar2
4 is
5 l_str varchar2(2000) default null;
6 l_sep varchar2(1) default null;
7 begin
8 for x in ( select b from t where a = p_a ) loop
9 l_str := l_str || l_sep || x.b;
10 l_sep := '-';
11 end loop;
12 return l_str;
13 end;
14 /

Function created.

ask_tom@OSI1.WORLD>
ask_tom@OSI1.WORLD> select a, get_transposed( a ) t
2 from t
3 group by a
4 /

A T
------------------------- ------------------------------
210 5000-5001-5002
220 6001-6002

Our next example is more complex. We will pass in the name of the 'key' column (the column to pivot on), a value for that column, the name of the column to actually select out and string together and finally the table to select from:

ask_tom@OSI1.WORLD> create or replace
2 function transpose( p_key_name in varchar2,
3 p_key_val in varchar2,
4 p_other_col_name in varchar2,
5 p_tname in varchar2 )
6 return varchar2
7 as
8 type rc is ref cursor;
9 l_str varchar2(4000);
10 l_sep varchar2(1);
11 l_val varchar2(4000);
12
13 l_cur rc;
14 begin
15
16 open l_cur for 'select '||p_other_col_name||'
17 from '|| p_tname || '
18 where ' || p_key_name || ' = :x '
19 using p_key_val;
20
21 loop
22 fetch l_cur into l_val;
23 exit when l_cur%notfound;
24 l_str := l_str || l_sep || l_val;
25 l_sep := '-';
26 end loop;
27 close l_cur;
28
29 return l_str;
30 end;
31 /

Function created.

ask_tom@OSI1.WORLD>
ask_tom@OSI1.WORLD> REM List the values of "B" for a given value
ask_tom@OSI1.WORLD> REM of "A" in the table "T"
ask_tom@OSI1.WORLD>
ask_tom@OSI1.WORLD> select a, transpose( 'a', a, 'b', 't' ) t
2 from t
3 group by a
4 /

A T
------------------------- ------------------------------
210 5000-5001-5002
220 6001-6002

Rating

(67 ratings)

We're not taking comments currently, so please try again later if you want to add a comment.

Comments

3 more methods

Mikito Harakiri, November 28, 2001 - 2:07 pm UTC

1. </code> https://asktom.oracle.com/magazine-archive.htm

Pitfalls: slower than 2 and 3

2.

http://asktom.oracle.com/magazine-archive.htm/code/tips2001/index.html?102801.html <code>
Essentially, this technique can be reduced to writing a package with a variable that accumulates the aggregated value ordering the records correctly and firing a method that calculates the aggregate. No need in ROLLUP or GROUPING.

3. 9i:

SELECT deptno, CURSOR(SELECT * FROM emp where deptno=dept.deptno) nested_TABLE
FROM dept;

-- User defined agregate through pipelined function
CREATE or replace FUNCTION CONCAT_LIST( cur SYS_REFCURSOR )
RETURN VARCHAR2 IS
ret VARCHAR2(32000);
tmp VARCHAR2(4000);
BEGIN
loop
fetch cur into tmp;
exit when cur%NOTFOUND;
ret := ret || ',' || tmp;
end loop;
RETURN ret;
END;
/

SELECT distinct
deptno,
SUBSTR(CONCAT_LIST(CURSOR(
SELECT ename FROM emp ee WHERE e.deptno = ee.deptno
)),1,40) employees
FROM emp e;

November 28, 2001 - 2:24 pm UTC

Or, even better, in 9i -- use a user defined aggregate so you don't have run the concat_list function once per row and then through out the dups (it would be better to write:

select deptno, SUBSTR(CONCAT_LIST(CURSOR(
SELECT ename FROM emp ee WHERE e.deptno = ee.deptno
)),1,40) employees
from ( select distinct deptno from emp );

)

But a good general purpose solution is:

scott@ORA9I.WORLD> create or replace type StringAggType as object
2 (
3 theString varchar2(4000),
4
5 static function
6 ODCIAggregateInitialize(sctx IN OUT StringAggType )
7 return number,
8
9 member function
10 ODCIAggregateIterate(self IN OUT StringAggType ,
11 value IN varchar2 )
12 return number,
13
14 member function
15 ODCIAggregateTerminate(self IN StringAggType,
16 returnValue OUT varchar2,
17 flags IN number)
18 return number,
19
20 member function
21 ODCIAggregateMerge(self IN OUT StringAggType,
22 ctx2 IN StringAggType)
23 return number
24 );
25 /

Type created.

scott@ORA9I.WORLD>
scott@ORA9I.WORLD> create or replace type body StringAggType
2 is
3
4 static function ODCIAggregateInitialize(sctx IN OUT StringAggType)
5 return number
6 is
7 begin
8 sctx := StringAggType( null );
9 return ODCIConst.Success;
10 end;
11
12 member function ODCIAggregateIterate(self IN OUT StringAggType,
13 value IN varchar2 )
14 return number
15 is
16 begin
17 self.theString := self.theString || ',' || value;
18 return ODCIConst.Success;
19 end;
20
21 member function ODCIAggregateTerminate(self IN StringAggType,
22 returnValue OUT varchar2,
23 flags IN number)
24 return number
25 is
26 begin
27 returnValue := rtrim( ltrim( self.theString, ',' ), ',' );
28 return ODCIConst.Success;
29 end;
30
31 member function ODCIAggregateMerge(self IN OUT StringAggType,
32 ctx2 IN StringAggType)
33 return number
34 is
35 begin
36 self.theString := self.theString || ',' || ctx2.theString;
37 return ODCIConst.Success;
38 end;
39
40
41 end;
42 /

Type body created.

scott@ORA9I.WORLD>
scott@ORA9I.WORLD> CREATE or replace
2 FUNCTION stringAgg(input varchar2 )
3 RETURN varchar2
4 PARALLEL_ENABLE AGGREGATE USING StringAggType;
5 /

Function created.

scott@ORA9I.WORLD>
scott@ORA9I.WORLD> column enames format a30
scott@ORA9I.WORLD> select deptno, stringAgg(ename) enames
2 from emp
3 group by deptno
4 /

DEPTNO ENAMES
---------- ------------------------------
10 CLARK,KING,MILLER
20 SMITH,FORD,ADAMS,SCOTT,JONES
30 ALLEN,BLAKE,MARTIN,TURNER,JAME
S,WARD

scott@ORA9I.WORLD>

Final Question About transposing

Edgars, March 07, 2002 - 2:09 am UTC

Hello Tom!

This article is very useful, but I think there is one thing that could be added to it. Is there a way to fully transpose table? For example suppose I have a table like this:

create table ts1( tm date, val float, descr varchar(5) );

And values like:

insert into ts1 values ( to_date( '01.01.2001.', 'DD.MM.YYYY.' ), -1, 'P1' );
insert into ts1 values ( to_date( '01.01.2001.', 'DD.MM.YYYY.' ), -2, 'P2' );
insert into ts1 values ( to_date( '01.01.2001.', 'DD.MM.YYYY.' ), -3, 'P3' );
insert into ts1 values ( to_date( '02.01.2001.', 'DD.MM.YYYY.' ), -1, 'P1' );
insert into ts1 values ( to_date( '02.01.2001.', 'DD.MM.YYYY.' ), -2, 'P2' );
insert into ts1 values ( to_date( '02.01.2001.', 'DD.MM.YYYY.' ), -3, 'P3' );
insert into ts1 values ( to_date( '03.01.2001.', 'DD.MM.YYYY.' ), -1, 'P1' );
insert into ts1 values ( to_date( '03.01.2001.', 'DD.MM.YYYY.' ), -2, 'P3' );
insert into ts1 values ( to_date( '04.01.2001.', 'DD.MM.YYYY.' ), -1, 'P1' );
insert into ts1 values ( to_date( '05.01.2001.', 'DD.MM.YYYY.' ), -1, 'P1' );
insert into ts1 values ( to_date( '05.01.2001.', 'DD.MM.YYYY.' ), -2, 'P2' );
insert into ts1 values ( to_date( '05.01.2001.', 'DD.MM.YYYY.' ), -3, 'P3' );

All I need is to get a table like this:
TM P1 P2 P3
01.01.2001. -1 -2 -3
02.01.2001. -1 -2 -3
03.01.2001. -1 NULL -3
04.01.2001. -1 NULL NULL
05.01.2001. -1 -2 -3

Can this be done internally in oracle? For now I use a crosstable to achieve this. Thank you.

March 07, 2002 - 7:59 am UTC

See

...
If you know the range of values for the column you wish to "list", you should
use DECODE(). An example of using decode to do this is at:

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:124812348063 <code>

Now, I will show 2 more ways to do it by calling PLSQL from sql. The ......

That shows (given you know p1, p2, p3) how to use decode to do that.

Followup to my previous comment

Edgars, March 07, 2002 - 2:23 am UTC

Hello Tom!

What I need in my comment can be achieved with decode if I know exectly values for descr (distinct descr). But what should I do when I don't know what distinct values are there?
Thank you!

March 07, 2002 - 8:07 am UTC

A SQL query *must* have a fixed number of columns (its one of the rules).

So, if you want to transpose in SQL, you need to know the columns in advance. If you didn't know the domain of descr was (P1, P2, P3) -- you would not know the number of columns -- hence you could not write the SQL (you could return a list like above, you could return a cursor for each row or you could use a collection type and return a single column that was in fact an array of values, eg:

scott@ORA817DEV.US.ORACLE.COM> select tm, CURSOR( select descr, val from ts1 x where x.tm = ts1.tm )
2 from ( select distinct tm
3 from ts1 ) ts1
4 /

TM CURSOR(SELECTDESCR,V
--------- --------------------
01-JAN-01 CURSOR STATEMENT : 2

CURSOR STATEMENT : 2

DESCR VAL
----- ----------
P1 -1
P2 -2
P3 -3

02-JAN-01 CURSOR STATEMENT : 2

CURSOR STATEMENT : 2

DESCR VAL
----- ----------
P1 -1
P2 -2
P3 -3

03-JAN-01 CURSOR STATEMENT : 2

CURSOR STATEMENT : 2

DESCR VAL
----- ----------
P1 -1
P3 -2

04-JAN-01 CURSOR STATEMENT : 2

CURSOR STATEMENT : 2

DESCR VAL
----- ----------
P1 -1

05-JAN-01 CURSOR STATEMENT : 2

CURSOR STATEMENT : 2

DESCR VAL
----- ----------
P1 -1
P2 -2
P3 -3

scott@ORA817DEV.US.ORACLE.COM>

Here you get a cursor for each row -- so there were only 5 rows returned -- each with a result set contained therein.

Also, if you have my book -- I have a routine in there that dynamically builds a query that does the transpose without having to know the domain up front. The stored procedure returns a ref cursor (result set) that is the transposed data.

Function to concatenate output

A reader, October 23, 2002 - 12:54 pm UTC

hi tom,

I don't know if this question will fit in here
because the question was for 8i and then 9i discussion started up.

but I want to know in the 8.1.7 db.

I have a pl/sql record consist of 5 field
and I have declared a type

v_rec new_data%rowtype;

I can access the elements by v_rec.field1..

but I want to convert the v_rece obj. in a string.

varchar2(2000) v_rec_str := fn (v_rec);

should move
v_rec.field1||v_rec.field2||.....

do we have any type casing function that will do this
or Ihave to manually
concatinate all the fields
convert to_cher if numeber
and assign to a varchar variable ?

October 23, 2002 - 2:00 pm UTC

manually concatenate, yes.

very helpful..thanks Tom.

A reader, November 07, 2002 - 7:30 pm UTC

Aggregate Function in Package

Jan, June 26, 2003 - 8:38 am UTC

Hi Tom,

I want to create my agg. function, it works for a standalone function, but not for the packaged one. What is the syntax?

Thanks

June 26, 2003 - 9:48 am UTC

It does not appear to be supported -- the aggregate map is a simple standalong function only.

Carsten Laube, August 01, 2003 - 11:50 am UTC

Hi Tom,

the stringaggtype is really smart. You can handle the stringagg-function like sum() or count().

Thanks a lot!

XMLTransform and XMLAgg [mis]use :-)

msc, September 25, 2003 - 10:37 am UTC

What a wonderful world - Oracle 9.2 includes XMLAgg() and XMLTransform():

select rtrim( extractvalue
( XMLTransform
( XMLElement( "tables", XMLAgg( XMLElement( "table", TABLE_NAME)))
, XMLTYPE( '<xsl:stylesheet version="1.0" xmlns:xsl="</code> http://www.w3.org/1999/XSL/Transform" <code>>
<xsl:template match="tables"><result><xsl:apply-templates/></result></xsl:template>
<xsl:template match="table"><xsl:apply-templates/>, </xsl:template>
</xsl:stylesheet> ')), '/result' ),', ')
from USER_TABLES
where rownum < 10;

PS 2 Tom: is it possible to disable URL transformation into hyperlink in review text? At least preview shows xmlns:xsl URL trsansformed to active hyperlink...

September 25, 2003 - 11:25 pm UTC

put a space in the http -- make it be h ttp or something. i just look for </code> http:// <code>

Please type PL/SQL in text editor.

Antony Paul, January 21, 2004 - 3:12 am UTC

I am finding this site very useful. Usually I ask questions at OTN forums and people provide links to this page. I have a request to you if possible type in the PL/SQL code in text editor and copy this to the site instead of that from SQL Plus which contains line numbers and causes difficulty incopy & paste.

January 21, 2004 - 6:43 am UTC

sorry -- cut and paste is the ONLY way to do it properly. It is the only way to avoid mistakes.

I use this all of the time myself. using my favorite editor vi, it takes exactly two commands to "fix the code"

:1,$s/.*WORLD>/xxxx/
:1,$s/.....//

You should have some equivalent in your text editor. It takes me about 2 seconds to go from this page to something I can re-run in sqlplus.

Without using a cut and paste from sqlplus, you would not get my narrative inlined with the output -- you'd see a script, followed by a ton of output and have to page up and down. i don't think it is too hard to perform two simple edits to re-run the code.

Please type PL/SQL in text editor

Antony Paul, January 22, 2004 - 4:44 am UTC

I learned regular expressions to remove numberings and now using it in a text editor to remove numberings and then copy to SQL Plus.

Antony Paul

Tom doesn't listen to his users

Mikito Harakiri, January 23, 2004 - 5:43 pm UTC

1. Not everybody uses vi.
2. Not every editor has regular expressions in it.
3. SQLPlus is the client that gives a bad reputation to oracle and oracle GUI development abilities, in particular. See </code> http://troels.arvin.dk/db/rdbms/links/ <code>"Annoying Oracle".

January 23, 2004 - 6:42 pm UTC

Mikito -- the contrarian.

anyway -- assume I didn't listen to them. Hmmm, would this place even exist? Rhetorical question, of course it wouldn't. I listen to them all day, pretty much all day.

Maybe you didn't listen to me -- the answers would NOT make sense unless they were in fact cut and pastes. Think about it. it would just be a bunch of code -- no results, no output from sqlplus.

If -- IF -- you cannot figure out how to get your editor to do those two global changes -- THEN -- well, I feel somewhat "sorry for you".

You either have the worst editor on the planet or..... well, I won't say the or outloud.

Tell me this Mr. Harakiri -- if it were not for sqlplus -- how, how the heck would this site even exist. Tell me that. I should have 5 meg pages with screen shots of CHARACTER MODE DATA???

funny thing is, people keep emailing me junk like that. screen shots of their windoze screen -- 1, 2, 3,... N megabytes big -- think nothing of it. Kills me when they do that (not that I asked them to, they just send it)

I find sqlplus to be the most enjoyable of environments.

If it were not for the character mode sqlplus -- this site would not exist.

If it were not for the character mode sqlplus -- my books would not be very readable. full of "pretty pictures" no doubt but not as good.

If you don't like it -- well, guess you don't have to come here and read.

Think about it -- if I used a gui -- you wouldn't even be able to cut and paste it would you. Nope, not really.

You should be saying "thank goodness for this simple, yet complete tool"

Tom's Miracle Ear

Mark A. Williams, January 23, 2004 - 8:24 pm UTC

> 1. Not everybody uses vi.
True, but I found no place where Tom claimed this to be true - only that it was his favorite editor.

> 2. Not every editor has regular expressions in it.
True, including the "alternative" from the link you provided. But surely line numbers can be removed without using regular expressions? It may be easier to do with regular expressions, but not required. Besides, most of Tom's examples are not 500 lines long.

> 3. SQLPlus is the client that gives a bad reputation to oracle...
</code> http://troels.arvin.dk/db/rdbms/links/ <code>

A link to a page on the site of a guy who is a student at "IT University of Copenhagen", says "PostgreSQL - my favorite, because of the nice feature set", and doesn't like sql plus (listing a java/jdbc utility as an alternative)... I have to say that my experience with Oracle has been (apparently) radically different from that of Mr. Arvins. Different strokes for different folks I guess. However, saying that Tom doesn't listen to his users is plain silly in my opinion. I believe he has been rather indulgent in your case in fact. I do find myself wondering what keeps bringing you back, but, like I said earlier, different strokes for different folks.

- Mark

sql*plus from my point of view ...

j., January 24, 2004 - 6:45 am UTC

... after reading tom's books and customizing sql*plus the way he described it became my tool of choice whenever i've to do things like spooling and tracing and such.

i prefer it to sql navigator for instance since sql*plus does *NOT* parse a statement *TWICE* for executing it just *ONCE* ... ;o)

Re: Tom's Miracle Ear

Troels Arvin, January 24, 2004 - 8:10 am UTC

Mark, what are you getting at?

What am i getting at...

Mark A. Williams, January 24, 2004 - 11:35 am UTC

Hi Troels,

I did not do a very good job of just trying to point out that you have a certain viewpoint or perspective that may (or probably) is different from the "typical" Oracle user or reader of AskTom. I felt that your perspective should be taken into consideration when evaluating your statements, etc., just as mine should, Tom's should, etc.

I guess my main confusion was that I don't see how your feelings about sql plus really substantiate mikito's claim that "SQLPlus is the client that gives a bad reputation to oracle and oracle GUI development abilities, in particular." Some of the things you mention like command line completion are not important to me, but perhaps they are to other people. So, for those people, they may be biased against sql plus from the start is my point stated differently perhaps. If I provided a link to a page on a person's site that said sql plus is the greatest tool in the whole world, would that be effective? I see that as being no more effective than a link to a page on a site that says sql plus is terrible.

As I said in my other post, I believe your experiences with Oracle have been different from mine. As a very pro-Oracle person, I do realize that not everyone has the same perspective as me. That should be taken into account as well.

In any case, I do hope that I did not cause any offense as certainly none was intended. I apoligize for my lack of clarity which probably has not been further focused by this post.

- Mark

What century do you guys live?

Mikito Harakiri, January 26, 2004 - 12:17 pm UTC

I don't know what to say. A relational DBMS manages data in the relational data model, whose primary construct is the table. And you advocate the client that cannot even properly render a table???

January 26, 2004 - 1:05 pm UTC

huh?

I don't know what to say to your not knowing what to say since I've not any clue what you mean by "render a table".

If you want a spreadsheet -- have fun with excel.

If you cannot figure out how to cut and paste and use your editor to fix up spoon fed, complete examples -- well, all I can say is "sorry, guess the gui has made the brain goto mush".

Just think, I could post pictures of a really pretty gui. Would that not be sooooo 2004 and hip. It would also make each page 50meg in size and sort of obviate this entire discussion since cutting and pasting would just be "so not happening"

I advocate people use the tools they like.

I like sqlplus

You do not have to like sqlplus. (hey -- you don't even like analytics, not "pure enough" for you - circular arguments here Mikito, you like what you like -- i obviously like what I like)

whatever, last followup i'll make on this. have a nice day.

ordering stringagg output

bob, March 15, 2004 - 7:24 pm UTC

Tom,

I hope you still have energy to follow up on a real question/comment. Using StringAgg, how can I order the concatenation so that it occurs alphabetically?

March 15, 2004 - 7:52 pm UTC

You'd have to "save up" the inputs and then sort them and then concatenate them. they come in random order to stragg...

more ordering stringagg output

dusty, April 22, 2004 - 8:31 pm UTC

The followup was concatenated!

Followup:

You'd have to "save up" the inputs and then sort them and then concatenate them.
they come in random order to stragg...

Fill in the blanks, please!

Also, I need to order by things that aren't in the string, ie,

select .....
strAgg(collector_name
[ordered by collector_order, a numeric value in table collector]),
strAgg(part_name [ordered by part_names, alphabetically]).....

April 23, 2004 - 10:19 am UTC

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:15637744429336#16551777586484 <code>

Removing Line numbers in sqlplus

dharma, May 06, 2004 - 7:04 pm UTC

You have used vi and showed us how to remove the line numbers which come from the cut and paste

I tried using
:1,$s/.*COM>/xxxx/

and it shows no changes to make since it cant find the pattern .*COM> if I am right. I copied the above command from one of your answers, checked the one above also.
I couldnt get any further. What am I doing wrong or what should I do? Still learning vi :)

Thanks a lot,
-dharma.

May 07, 2004 - 7:03 am UTC

well, look at the prompt I used in the example

.*COM>

won't match:

ask_tom@OSI1.WORLD> create table t
2 ( a varchar2(25),
3 b varchar2(25)
4 );

(obviously -- .*RLD> would match....)

but in another place on this page:

scott@ORA817DEV.US.ORACLE.COM> select tm, CURSOR( select descr, val from ts1 x
where x.tm = ts1.tm )
2 from ( select distinct tm
3 from ts1 ) ts1
4 /

it would match (.*COM> would)

basically you are taking the sqlplus prompt and converting it into four xxxx's. Look at the prompt used and take the last couple of letters and the > sign and use that in the template.

99.9999% of the time, it'll be .*COM> on this site.

Get the most recent

Patrick, November 19, 2004 - 5:58 pm UTC

Hi Tom, I want to get the most recent record from a table. This table contains two column:

CREATE TABLE TstDate (
Issue varchar(10),
Installed timestamp
);

And here are the values that I add:

INSERT INTO TstDate VALUES ( 'V.1', '1980-01-01 01:00:00.000');
INSERT INTO TstDate VALUES ( 'V.2', '1990-01-01 01:00:00.000');
INSERT INTO TstDate VALUES ( 'V.3', '2000-01-01 01:00:00.000');
INSERT INTO TstDate VALUES ( 'V.1', '1980-01-01 02:00:00.000');

Notice that both issue 'V.1' are not installed at the same time.

And here is the output that I want:

INSTALLED ISSUE
-------------------------------------- ----------
1980-01-01 02:00:00.000000 V.1
1990-01-01 01:00:00.000000 V.2
2000-01-01 01:00:00.000000 V.3

Is there an easy way to achieve that? I tried with MAX() but not successful...

Thanks for your help.

November 19, 2004 - 8:01 pm UTC

</code> https://www.oracle.com/technetwork/issue-archive/2012/12-sep/o52asktom-1735913.html <code>

see playing with aggregation....

concat

SC, December 13, 2004 - 10:38 am UTC

Hi Tom.

create table concat_test (x number(4), y date, z varchar2(5))
/

begin
for i in 1..120000
loop
insert into concat_test values
( dbms_random.value(1,9999), sysdate-dbms_random.value(1,7), dbms_random.string('A',5) );
end loop;
end;
/
commit;

1.select x,y,z
from
concat_test

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 8001 0.37 0.37 0 8357 0 120000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 8003 0.38 0.37 0 8357 0 120000

2. select x||'|'||y||'|'||z
from
concat_test

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 8001 0.99 0.91 0 8357 0 120000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 8003 0.99 0.92 0 8357 0 120000

Why is select 2 slower and consumes more cpu than select 1
Some kind of conversion?

December 13, 2004 - 1:39 pm UTC

it is not obvious?

f(x) is going to be slower than x alone...

not bad -- 5.083333e-6 seconds to concatenate 5 strings together.

Back to String Aggregation topic

Stew Stryker, April 28, 2005 - 11:14 am UTC

NOT to beat a dead horse, but I found all the function solutions pretty complex to implement. (Maybe I missed one?) If I can humbly offer up my solution for personal scrutiny and/or bashing...

This overloaded, packaged function accepts either a CURSOR or a string representation of the query as the main argument. The CURSOR version can only be used within the SQL context, while the string version can also be used in the PL/SQL context.

CREATE OR REPLACE PACKAGE misc_tools is

TYPE single_string_rec_t IS RECORD(
string_val VARCHAR2(256));

TYPE string_ref_cur IS REF CURSOR;

-- Given single-column ref cursor, return values separated by delimiter parameter value
FUNCTION cursor_to_list(
cur_in IN misc_tools.string_ref_cur,
delimiter_in IN VARCHAR2 := ', ')
RETURN VARCHAR2;

-- Given single-column ref cursor, return values separated by delimiter parameter value
-- Overloaded to accept query string instead of opened cursor
FUNCTION cursor_to_list(
select_query_in IN VARCHAR2,
delimiter_in IN VARCHAR2 := ', ')
RETURN VARCHAR2;

end misc_tools; /* End of package header specification */
/
CREATE OR REPLACE PACKAGE BODY misc_tools is

FUNCTION cursor_to_list(
cur_in IN misc_tools.string_ref_cur,
delimiter_in IN VARCHAR2 := ', ')
RETURN VARCHAR2
IS
/*
Description: Given single-column ref cursor, return values separated
by delimiter parameter value.

Trim last trailing delimiter.

Parameters: cur_in - opened cursor w/ single string column result
delimiter_in - separate values w/ this, defaults to comma-space

Returns: String of separated values

Usage: Within a SQL statement, you pass a cursor to this function using
the CURSOR clause, as follows:

SELECT DISTINCT id_number,
misc_tools.cursor_to_list(CURSOR (SELECT child_id_number
FROM children c
WHERE c.id_number = p.id_number
ORDER BY child_id_number)) childs
FROM parent p
WHERE p.id_number = '0000000006';

Note that this will ONLY work in the context of a SQL statement.
For use within PL/SQL context, see the other variant below.
*/
row_value single_string_rec_t;
ret_list VARCHAR2(32000);
BEGIN
IF cur_in%ISOPEN
THEN
FETCH cur_in
INTO row_value;

WHILE cur_in%FOUND
LOOP
ret_list := ret_list || row_value.string_val || delimiter_in;
FETCH cur_in
INTO row_value;
END LOOP;

-- Strip last delimiter
ret_list := RTRIM(ret_list, delimiter_in);
CLOSE cur_in;
END IF;

RETURN ret_list;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
RETURN ret_list;
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line('EXCEPTION IN misc_tools.cursor_to_list - '
|| TO_CHAR(SQLCODE)
|| ': '
|| SQLERRM);
RAISE;
RETURN ret_list;
END cursor_to_list;

-- Overloaded to accept query string instead of opened cursor
FUNCTION cursor_to_list(
select_query_in IN VARCHAR2,
delimiter_in IN VARCHAR2 := ', ')
RETURN VARCHAR2
IS
/*
Description: query string that will be opened dynamically,
return values separated by delimiter parameter value.

Trim last trailing delimiter.

Parameters: select_query_in - SELECT statement, so cursor is created dynamically
delimiter_in - separate values w/ this, defaults to comma-space

Returns: String of separated values

Usage: This version may be called from SQL or PL/SQL contexts.

children_list := misc_tools.cursor_to_list(
select_query_in => 'SELECT empno FROM scott.emp WHERE mgr = ' || mgr_id,
delimiter_in => ', '
);

*/
ret_list VARCHAR2(32000);
local_cur string_ref_cur;
BEGIN
OPEN local_cur
FOR select_query_in;

ret_list := cursor_to_list(cur_in => local_cur, delimiter_in => delimiter_in);
RETURN ret_list;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line('EXCEPTION IN misc_tools.cursor_to_listV2 - '
|| TO_CHAR(SQLCODE) || ': ' || SQLERRM);
RAISE;
RETURN ret_list;
END cursor_to_list;

END misc_tools;
/

Concat column values in one SQL SELECT statement (without PLSQL)

Denis &#272;uki&#263;, September 22, 2005 - 9:03 am UTC

Hi,
here is solution in one SELECT, isn't elegant at all, and have some drawbacks, so ...

CREATE TABLE t ( DATA VARCHAR2(20));

INSERT INTO t SELECT 'STRING_' || ROWNUM FROM DUAL CONNECT BY ROWNUM < 301;
COMMIT ;

SELECT SUBSTR (c, 2) concatenated
FROM (SELECT SYS_CONNECT_BY_PATH (DATA, ',') c, r
FROM (SELECT ROWNUM ID, DATA,
RANK () OVER (ORDER BY ROWID DESC) r
FROM t
ORDER BY DATA)
START WITH ID = 1
CONNECT BY PRIOR ID = ID - 1)
WHERE r = 1;

Concat column values in one SQL SELECT statement (without PLSQL)

Denis &#272;uki&#263;, September 23, 2005 - 3:57 am UTC

Hi,
this version work with a views too.

DROP TABLE t;
CREATE TABLE t ( s VARCHAR2(20));

INSERT INTO t SELECT 'STRING_' || ROWNUM FROM DUAL CONNECT BY ROWNUM < 301;
COMMIT ;

SELECT SUBSTR (c, 2) concatenated
FROM (SELECT SYS_CONNECT_BY_PATH (s, ',') c, r
FROM (SELECT RANK () OVER (ORDER BY s ASC) ID, s,
RANK () OVER (ORDER BY s DESC) r
FROM t)
START WITH ID = 1
CONNECT BY PRIOR ID = ID - 1)
WHERE r = 1;

Concat column values in one SQL SELECT statement

Denis &#272;uki&#263;, September 23, 2005 - 6:29 am UTC

Find a bug in above version (sorry).
Here:

CREATE TABLE t ( s VARCHAR2(20));

INSERT INTO t SELECT 'STRING_' || ROWNUM FROM DUAL CONNECT BY ROWNUM < 301;
COMMIT ;

SELECT SUBSTR (c, 2) concatenated
FROM (SELECT SYS_CONNECT_BY_PATH (s, ',') c, r
FROM (SELECT ROWNUM ID, s,
RANK () OVER (ORDER BY ROWNUM DESC) r
FROM t
ORDER BY s)
START WITH ID = 1
CONNECT BY PRIOR ID = ID - 1)
WHERE r = 1;

StringAgg and Parallel

BC, October 25, 2005 - 3:57 pm UTC

Tom,

StringAgg returns multiple comma's when the tables default degree of parallelism is set. Can you help me resolve this issue ? I copied and pasted the code to create StringAgg from your reply above in this post.

create table parallel_df
( func_id number(3,0) not null,
value_code varchar2(10)
)
parallel (degree default)
/

insert into parallel_df values(254,'CA');
insert into parallel_df values(254,'MN');
insert into parallel_df values(254,'LA');
insert into parallel_df values(225,'S');
insert into parallel_df values(225,'M');
insert into parallel_df values(225,'L');
insert into parallel_df values(225,'F');
insert into parallel_df values(226,'AP');
insert into parallel_df values(226,'AN');
insert into parallel_df values(226,'GEN');
insert into parallel_df values(226,'SDMD');
insert into parallel_df values(254,'NY');
insert into parallel_df values(254,'ME');
insert into parallel_df values(254,'NJ');
insert into parallel_df values (254,'FL');
commit;

select func_id,
stringagg(value_code)
from parallel_df
group by func_id;

FUNC_ID STRINGAGG(VALUE_CODE)
---------- ------------------------------------
225 S,,L,F,M
226 AN,AP,GEN,SDMD
254 FL,LA,NY,,ME,NJ,,CA,MN

--Notice the extra comma's in the output

alter table parallel_df noparallel;

select func_id,
stringagg(value_code)
from parallel_df
group by func_id;

FUNC_ID STRINGAGG(VALUE_CODE)
---------- -------------------------------------
225 S,M,L,F
226 AP,AN,SDMD,GEN
254 CA,MN,ME,FL,NJ,NY,LA

Your help is highly appreciated.

Thanks

BC

October 26, 2005 - 11:29 am UTC

looks like the merge routine:

36 self.theString := self.theString || ',' || ctx2.theString;

remove the comma - what then?

Awesome

BC, October 26, 2005 - 2:27 pm UTC

Tom,

Thank you so much for the quick response.

It works fine now, Can you explain why?

member function ODCIAggregateMerge(self IN OUT StringAggType,
ctx2 IN StringAggType)
return number
is
begin
--self.theString := self.theString || ',' || ctx2.theString;
self.theString := self.theString || ctx2.theString;
return ODCIConst.Success;
end;

With parallel (degree default)

FUNC_ID STRINGAGG(VALUE_CODE)
---------- ----------------------------
254 FL,NY,CA,MN,ME,NJ,LA
226 AN,GEN,SDMD,AP
225 S,M,L,F

With noparallel

FUNC_ID STRINGAGG(VALUE_CODE)
---------- ----------------------------
225 S,M,L,F
226 AP,AN,SDMD,GEN
254 CA,MN,ME,FL,NJ,NY,LA

October 27, 2005 - 4:42 am UTC

...
21 member function ODCIAggregateTerminate(self IN StringAggType,
22 returnValue OUT varchar2,
23 flags IN number)
24 return number
25 is
26 begin
27 returnValue := rtrim( ltrim( self.theString, ',' ), ',' );
28 return ODCIConst.Success;

....

because my logic puts an extra ',' on the front of the string, which my terminate trimmed off - but the merge did not ;)

Thanks

BC, October 27, 2005 - 1:53 pm UTC

Tom,

Thank you very much for your quick and informative reply.

A reader, January 05, 2006 - 12:52 pm UTC

Hi Tom,

I am having a requirement something like this:

I have a database where the dateofbirth values are stored in a varchar column in the format yyyymmdd. I want the output to come as mm-dd-yyyy. What is the easiest way to do that? Also, I have the ssn stored as contiguous digits but I want to insert - in between like ###-##-####.

Please help.

January 05, 2006 - 1:22 pm UTC

....
I have a database where the dateofbirth values are stored in a varchar column in
the format yyyymmdd....

no you don't, you have a database full of strings :) there are no dates to be found in that database. (just ask the optimizer, it'll tell you that, especially when it makes mistakes on cardinalities... Ask yourself

how many strings between 20001231 20010101
how many DAYS between December 31, 2000 and January 1, 2001

The easiest way to achieve your goal - use a date, it is what you meant to use in the first place.

The hard ways, keep it in a string, then

to_char( to_date( dateofbirth,'yyyymmdd' ), 'mm-dd-yyyy' )

but be prepared to find someone born on the 29th of Feb, 2001 sometime, or someone born on the 12th day of the 13th month of the year 9999.

Or use substr to substr out the characters and put them back together.

I'd go with a DATE type myself - that way I'd find all of my bad data, fix it once and life would be good from there on.

As for the SSN, you have a string again. Same discusssion repeated... Substr it.

Great info, Thanks...

Jim Schwarz, July 27, 2006 - 12:18 pm UTC

Tom's first post answered my question completely. I was able to apply it immediately to my situation. Thanks for providing this valuable information in such a timely way. Thanks to those on the thread who managed to keep it to the point.

Review of concatenating rows

blanche, August 02, 2006 - 2:52 pm UTC

Definitely the solution to my problem (the sys_connect_by_path approach). Much easier in a very low-volume situation than creating the aggregate function Tom sugggested at the beginning of this thread.

However, now I need to figure out how to alphabetize the list. My data is a number of rows with only 1 column, the name of a state (e.g. Maine, Colorado, etc). The list comes out ordered by the row number. Any suggestions how to order by the state?

August 03, 2006 - 7:49 am UTC

ops$tkyte%ORA10GR2> with data
  2  as
  3  (
  4  select job,
  5         ename,
  6         row_number() over (partition by job order by ename) rn,
  7         count(*) over (partition by job) cnt
  8    from emp
  9  )
 10  select job, ltrim(sys_connect_by_path(ename,','),',') scbp
 11    from data
 12   where rn = cnt
 13   start with rn = 1
 14  connect by prior job = job and prior rn = rn-1
 15   order by job
 16  /

JOB       SCBP
--------- ----------------------------------------
ANALYST   FORD,SCOTT
CLERK     ADAMS,JAMES,MILLER,SMITH
MANAGER   BLAKE,CLARK,JONES
PRESIDENT KING
SALESMAN  ALLEN,MARTIN,TURNER,WARD


when you assign the row_number to the "group by" columns (the thing you want to pivot on, job in this example) - simply order by what  you want.

Review of concatenating rows, part 2

blanche, August 02, 2006 - 3:02 pm UTC

Urps...I guess I should have provided my code for comment:

SELECT substr(c, 2) state_list
FROM (SELECT sys_connect_by_path(state, ', ') c, r
FROM (SELECT rownum id,
initcap(c.rv_meaning) state,
rank() over(ORDER BY rownum DESC) r
FROM students cad,
student_class_year ccy,
cg_ref_codes c
/*
cg_ref_codes is a table that contains a mapping from the state abbreviation to the full-text name of the state
*/
WHERE cad.pid = ccy.cad_pid
AND ccy.cyr_year_identifier = '2005'
AND cad.state = c.rv_low_value
AND c.rv_domain = 'STATE CODES'
ORDER BY c.rv_meaning)
START WITH id = 1
CONNECT BY PRIOR id = id - 1)
WHERE r = 1

Review of concatenating rows, the followup

blanche, August 03, 2006 - 7:29 pm UTC

Um...if I had 2 columns and were using the partion approach, this would work. But I'm using the rank() over and must use the rownum to order. In your original example, you're ordering by the ename. When I try the "order by state" I get one state, not the entire list.

I gotta think some more on this.

August 04, 2006 - 7:43 am UTC

you gotta provide an example to accompany your words too maybe.

If I have N columns, using the partition approach, this would world

You shouldn't be using RANK(), that would not work.

You asked a question - recieved the technique.  rank would not be appropriate in the simplified query I gave there.


so you have a query that returns a list of state names (nothnig to partition by)

You want a sorted list.

easy - forget the three table join (you'll need it for YOUR result, we don't need it for OUR example)

we just need a source of text right:

(select ename from emp)

pretend that is your query that returns the states.

ops$tkyte%ORA10GR2> with data
  2  as
  3  (
  4  select ename, row_number() over (order by ename) rn, count(*) over () cnt
  5  from
  6  (
  7  select ename
  8    from emp
  9  )
 10  )
 11  select sys_connect_by_path(ename, ',') scbp
 12    from data
 13   where rn = cnt
 14   start with rn = 1
 15  connect by prior rn = rn-1
 16  /

SCBP
-------------------------------------------------------------------------------
,ADAMS,ALLEN,BLAKE,CLARK,FORD,JAMES,JONES,KING,MARTIN,MILLER,SCOTT,SMITH,TURNER
,WARD

ORA-00938: not enough arguments for function

Khurram, December 29, 2006 - 7:39 am UTC

Hi

Tom i dont know is this my question relevant from this thread or not but i tried to google out asktom fro this relevant thread but found only this thread relevant one,I have proble with this query with views,but not with plan table why?How can i overcome on it

Why not ORA-00938: not enough arguments for function with this SQL??

SQL> CONN scott/tiger@takaful
Connected.

SQL> SELECT empno,deptno
  2    FROM scott.emp
  3   WHERE deptno IN (DECODE(deptno,'10','20','30','30'),'40','50')
  4  /

     EMPNO     DEPTNO
---------- ----------
      7521         30
      7654         30
      7698         30
      7844         30
      7900         30



SQL> CONN pktcl/pktcl@takaful
Connected.
SQL> DESC vw_ins_outstanding
 Name                    Null?    Type
 ----------------------- -------- ----------------
 REC_TYPE                         VARCHAR2(7)
 ASSORTED_CODE                    VARCHAR2(12)
 CLAIM_CODE                       VARCHAR2(10)
 CLAIM_INFO_CODE                  VARCHAR2(10)
 CLASS_CODE                       VARCHAR2(2)
 PRINCIPAL_COMPANY                VARCHAR2(1)
 INSURANCE_TYPE_CODE              VARCHAR2(1)
 DUE_AMOUNT                       NUMBER
 DUE_DATE                         DATE
 MODE_CODE                        VARCHAR2(3)
 SEQ_NO                           NUMBER(28)
 COINSURANCE_CODE                 VARCHAR2(8)
 POLICY_CODE                      VARCHAR2(12)
 CHEQUE_NO                        VARCHAR2(20)
 CHEQUE_AMOUNT                    NUMBER
 CHEQUE_DATE                      DATE
 VOUCHER_NO                       VARCHAR2(20)
 VOUCHER_DATE                     DATE
 SUP_BY                           VARCHAR2(20)
 PAID_TO_CODE                     VARCHAR2(8)
 CANCEL_DATE                      DATE
 SUP_DATE                         DATE

Why ORA-00938: not enough arguments for function with this SQL??


SQL> SELECT mode_code
  2    FROM vw_ins_outstanding
  3   WHERE MODE_CODE IN (DECODE(PRINCIPAL_COMPANY,'D','24','34'),'31','02')
  4  .
SQL> /
 WHERE MODE_CODE IN (DECODE(PRINCIPAL_COMPANY,'D','24','34'),'31','02')
                     *
ERROR at line 3:
ORA-00938: not enough arguments for function

SQL> DISCONNECT
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

Khurram

December 29, 2006 - 10:02 am UTC

scott%ORA10GR2> SELECT mode_code
2 FROM (select dummy mode_code, dummy principal_company from dual) vw_ins_outstanding
3 WHERE MODE_CODE IN (DECODE(PRINCIPAL_COMPANY,'D','24','34'),'31','02')
4 /

no rows selected

I cannot reproduce - please utilize support.

Amendment

Khurram, December 29, 2006 - 7:42 am UTC

Sorry

but not with plan table why?

Instead

but not with plain table why?

Khurram

Another way to convert from table to comma

Hirdey, December 29, 2006 - 11:36 am UTC

Function Fn_Table2Comma(p_cursor in sys_refcursor) return clob as
x dbms_utility.uncl_array;
y BINARY_INTEGER;
z clob;
Begin
Fetch p_cursor bulk collect into x;
dbms_utility.table_to_comma(x, y, z);
return z;
end Fn_Table2Comma;

With 10g also SQL Model Clause may help

Tonguc, January 03, 2007 - 1:43 pm UTC

Tested below scripts on Oracle Database 10g Express Edition, Release 10.2.0.1.0;

drop table tbl_tst_mdl_clause purge ;
create table tbl_tst_mdl_clause (id varchar2(2), des varchar2(4), t number);
INSERT INTO tbl_tst_mdl_clause values(¿A',¿a1',10);
INSERT INTO tbl_tst_mdl_clause values(¿A',¿a2',20);
INSERT INTO tbl_tst_mdl_clause values(¿A',¿a3',30);
INSERT INTO tbl_tst_mdl_clause values(¿B',¿a1',40);
INSERT INTO tbl_tst_mdl_clause values(¿B',¿a2',50);
INSERT INTO tbl_tst_mdl_clause values(¿C',¿a3',60);
SELECT * FROM tbl_tst_mdl_clause ;

ID DES T
-- ---- ----------
A a1 10
A a2 20
A a3 30
B a1 40
B a2 50
C a3 60

select distinct i, A1, A2, A3
from tbl_tst_mdl_clause c
model
ignore nav
dimension by(c.id i,c.des d)
measures(c.t t, 0 A1, 0 A2, 0 A3)
rules(
A1[any,any] = t[cv(i),d = 'a1'],
A2[any,any] = t[cv(i),d = 'a2'],
A3[any,any] = t[cv(i),d = 'a3']
);

I A1 A2 A3
-- ---------- ---------- ----------
B 40 50 0
A 10 20 30
C 0 0 60

select distinct d, A, B, C
from tbl_tst_mdl_clause c
model
ignore nav
dimension by(c.id i,c.des d)
measures(c.t t, 0 A, 0 B, 0 C)
rules(
A[any,any] = t[i = `A¿, cv(d)],
B[any,any] = t[i = `B¿, cv(d)],
C[any,any] = t[i = `C¿, cv(d)]
);

D A B C
---- ---------- ---------- ----------
a1 10 40 0
a3 30 0 60
a2 20 50 0

explain plan set statement_id 'tonguc' for
select distinct d, A, B, C
from tbl_tst_mdl_clause c
model
ignore nav
dimension by( c.id i,c.des d)
measures(c.t t, 0 A, 0 B, 0 C)
rules(
A[any,any] = t[i = 'A', cv(d)],
B[any,any] = t[i = 'B', cv(d)],
C[any,any] = t[i = 'C', cv(d)]
);

select plan_table_output from table(dbms_xplan.display(¿plan_table¿,'tonguc¿));

..
| 0 | SELECT STATEMENT | | 6 | 120 | 3 (34)|00:00:01 |
| 1 | HASH UNIQUE | | 6 | 120 | 3 (34)|00:00:01 |
| 2 | SQL MODEL ORDERED | | 6 | 120 | | |
| 3 | TABLE ACCESS FULL| TBL_TST_MDL_CLAUSE | 6 | 120 | 2 (0)|00:00:01 |
..

"Why Use SQL Modeling?" from documenation;

Oracle modeling enables you to perform sophisticated calculations on your data. A typical case is when you want to apply business rules to data and then generate reports. Because Oracle Database integrates modeling calculations into the database, performance and manageability are enhanced significantly.

Like Mr.Kyte always suggests; "do it in a single SQL if possible, analytics rock-and-roll" Like Analytics it is not easy to understand the Model clause, but even when you try to understand I believe a new domain starts with SQL :)

Oracle® Database Data Warehousing Guide 10g Release 2 (10.2)
Chapter 22 SQL for Modeling http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14223/sqlmodel.htm#sthref1855

Best regards,
Tonguc

transpose return size > 4000

zile liao, January 10, 2007 - 12:57 pm UTC

Hi Tom,

I have read one of your response that to speed up clob operation, you can store clob data in multiple varchar2(4000) rows. If I have implemented that, what would be the easiest/efficient way to retrive the data into an application (i.e. java.lang.String)?

Thanks.

problem with NVARCHAR2 datatype

Rytis, November 30, 2007 - 1:15 pm UTC

The concatenate aggregate function returns nothing when working with NVARCHAR2 datatype columns.
ODCIAggregateTerminate seems also doesn't support NVARCHAR2 data type. The oracle connection is terminated on select execution, after this change:

MEMBER FUNCTION ODCIAggregateTerminate(self IN StringAggType,
returnValue OUT NVARCHAR2,
flags IN NUMBER)

November 30, 2007 - 4:32 pm UTC

if you are getting an ora-600|7445|3113 - please utilize support, that would be not expected.

Does not work with Oracle 11

rc, December 01, 2007 - 5:21 pm UTC

The select statement of Denis from Ljubljana (Slovenija), (Belgrade) Srbija, (Prijedor) BiH (see above) seems not to work in Oracle 11. 

CREATE TABLE t ( s VARCHAR2(20));

INSERT INTO t  SELECT     'STRING_' || ROWNUM FROM DUAL CONNECT BY ROWNUM < 301;
COMMIT ;

SELECT SUBSTR (c, 2) concatenated
  FROM (SELECT     SYS_CONNECT_BY_PATH (s, ',') c, r
              FROM (SELECT   ROWNUM ID, s,
                             RANK () OVER (ORDER BY ROWNUM DESC) r
                        FROM t
                    ORDER BY s)
        START WITH ID = 1
        CONNECT BY PRIOR ID = ID - 1)
 WHERE r = 1; 

no rows selected in Oracle 11! 

SQL> select * from v$version;

BANNER
----------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE    11.1.0.6.0      Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

It works fine in Oracle 10!!

Denis himself/herself made that change

rc, December 03, 2007 - 3:26 pm UTC

Denis changed RANK () OVER (ORDER BY ROWID DESC) r
into RANK () OVER (ORDER BY ROWNUM DESC).

His/her first statement used order by rowid desc, the third order by rownum desc.

Maybe some serious testing is needed.

Review in the future

rc, December 03, 2007 - 3:32 pm UTC

I don't get this, Laurent Schneider, has written a review in the future: December 4, 2007 - 3am US/Eastern. Now the order of the reviews is messed up!

order by rownum desc

Laurent Schneider, December 04, 2007 - 3:59 am UTC

RANK () OVER (ORDER BY ROWNUM DESC)

Denis used :
RANK () OVER (ORDER BY ROWID DESC) r

rownum

Laurent Schneider, December 04, 2007 - 5:03 am UTC

you are right, but it does not work with rownum...

select current_timestamp at time zone 'US/Eastern' from dual;

CURRENT_TIMESTAMPATTIMEZONE'US/EASTERN'
---------------------------------------
04-DEC-07 05.10.48.910583 AM US/EASTERN

Why not do like this?

Srini, August 05, 2008 - 7:55 pm UTC

Hi Tom,

I was going through this article. A method was suggested to go by XMLTRANSFORM and get the output in the desired format. Rather doing like that, why not we directly do like this.. (oracle 9i)

select rtrim(replace(replace(xmlagg(xmlelement("t",table_name)),'<t>',''),'</t>',','),',') from user_tables where rownum <10

August 05, 2008 - 10:09 pm UTC

sure, there are many ways - and that works.

date with constant time

Anurag, September 24, 2008 - 1:34 pm UTC

Hi Tom

I need to find some data for a day with fixed timings. Basic query like this is failing
select to_date(concat(to_char(to_date('24-sep-2008','dd-mon-yyyy')-1,'dd-mon-yyyy'),' 21:15'),'dd-mon-yyyy') from dual;
with error
ERROR at line 1:
ORA-01830: date format picture ends before converting entire inpuut string
Could you please help.

Thanks in advance

September 24, 2008 - 8:07 pm UTC

select to_date( '24-sep-2008', 'dd-mon-yyyy')-1 + 21/24 + 15/24/60 from dual;

if you look at yours it is:

to_date( '24-sep-2008' ) -1 ----- returns a DATE, the 23rd of sept, 2008

to_char( <that>, 'dd-mon-yyyy' ) ---- returns a string - 23-sep-2008

concat( <that>, ' 21:15' ) --- returns a string 23-sep-2008 21:15

to_date( <that>, 'dd-mon-yyyy' ) -- which fails because you didn't tell us how to convert 21:15!!!!! (date picture of dd-mon-yyyy ended before the string to be converted did)

I would not use your long approach - get the fixed date, add so many hours and minutes to it.

thnx

anurag, September 25, 2008 - 3:37 pm UTC

Thanks Tom!

I'll check the desired result from my query and will update.

regards,

string of values

DM, October 30, 2008 - 10:30 am UTC

Hi Tom
I am writing function to Return the string of values .it is like this ...

FUNCTION Check1 ( i_spec_id IN study_v.sp_id%TYPE,
i_dataset_nm IN dset.dset_nm%TYPE)
RETURN VARCHAR2
IS
l_sql VARCHAR2(4000) := NULL ;
BEGIN
DBMS_OUTPUT.PUT_LINE('l_sql1 = '||NULL);
----Get columns missing in staging table
For i IN(SELECT var_nm from (
SELECT var_nm
FROM spec_ds_v
WHERE dataset_nm = i_dataset_nm
AND spec_id = i_spec_id
MINUS
SELECT column_name
FROM ALL_TAB_COLUMNS
WHERE TABLE_NAME = i_dataset_nm||'_STAGE' ))
LOOP
l_sql := i.var_nm||',';
END LOOP;
l_sql := rtrim(l_sql,',');
RETURN l_sql;

EXCEPTION
WHEN OTHERS
THEN
NULL;
END Check1;

Tom it is not doing as expected ....Please can you help me with this .....or suggest me a better way to go for it.
Many thanks in Advance ....

November 02, 2008 - 3:04 pm UTC

      EXCEPTION
      WHEN OTHERS
      THEN
         NULL;
   END Check1;

I hate your code, that is all.
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QUESTION_ID:1277497900346822151

(ctl-f for "i hate" on that page to see why)

I'm not fond of your "detailed explanation" either...
I am writing function to Return the string of values

not very "detailed", you provide code that does not work, but NOT ANY EXPLANATION of what said code is *supposed to do*.

How about this:

I have this following code, it does not work, it is supposed to fargulate the values into garfaks.

an example of non-functioning code is omitted - for the simple reason that it would not help you understand the problem at all - it would only, could only confuse and mislead you.

there is a famous acronym in our industry

GIGO

you just got it.

Function for cancatinating output

shiva, December 10, 2008 - 10:35 am UTC

Hi Tom,

I have a tables called TALENT and TALENT_SKILL and having a one-many relationship. One of my UI screen requirement here is to display list of Talents and their comma separated skills.

I wrote a following function:

CREATE OR REPLACE FUNCTION TALENT_SKILLS_FUNC(t_id IN INTEGER) RETURN VARCHAR2 IS
RESULT VARCHAR2(1000);
BEGIN
WITH data AS(
SELECT myvalues,
row_number() OVER(ORDER BY myvalues) rn,
count(*) over() cnt
FROM (SELECT sp.prop_value myvalues
FROM LOOKUP lp,TALENT_SKILL ts
WHERE lp.lookup_id=ts.talent_skill_id
and ts.talent_id=t_id)
)
)
SELECT ltrim(sys_connect_by_path(myvalues, ','), ',') catvalues
INTO result
FROM data
WHERE rn = cnt
START WITH rn = 1
CONNECT BY PRIOR rn = rn - 1;
RETURN(result);
END TALENT_SKILLS_FUNC;

I am presently using following SQL query to fulfill my requirement:
SELECT t.talent_id,t.first_nm,t.last_name,TALENT_SKILLS_FUNC(t.talent_id) talent_skills FROM TALENT t;

But problem here is that, it is taking more time in retriving records. Appreciate,if you suggest me for any enhancements.

Thanks in advance

Shiva

December 10, 2008 - 1:46 pm UTC

You can just join and do this

ops$tkyte%ORA10GR2> create table talent
  2  as
  3  select dname tname, deptno pk
  4    from scott.dept
  5  /

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create table talent_skills
  2  as
  3  select deptno fk, ename skill
  4    from scott.emp
  5  /

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select pk, tname, substr(max(sys_connect_by_path(skill,',')),2) skills
  2    from (
  3  select t.pk, t.tname, ts.skill, row_number() over (partition by t.pk order by ts.skill) rn
  4    from talent t, talent_skills ts
  5   where t.pk = ts.fk
  6         )
  7  start with rn = 1
  8  connect by prior pk = pk and prior rn = rn-1
  9  group by pk, tname
 10  order by tname
 11  /

        PK TNAME          SKILLS
---------- -------------- ----------------------------------------
        10 Accounting     CLARK,KING,MILLER
        20 RESEARCH       ADAMS,FORD,JONES,SCOTT,SMITH
        30 SALES          ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

Concatenate multiple rows into single

Shiva, December 11, 2008 - 11:27 am UTC

Hi Tom,

Thanks very much for your valuable suggestion

Regards
Shiva

Function for cancatinating output

Shiva, December 26, 2008 - 2:34 am UTC

Hi Tom,

Based on your useful suggestion I modified the SQL code accordingly. After this modifiation response time in Searching UI changed from 12 secs to 8 secs. However business analysts expecting a response time at 2-4 secs. After analysing all the way, I concluded to create a materialized view with a FAST REFRESH ON COMMIT option. Here is the code I tried:

CREATE MATERIALIZED VIEW talent_skill_mv
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS
SELECT pk, tname, substr(MAX(sys_connect_by_path(skill,',')),2) skills
FROM (
SELECT t.pk, t.tname, ts.skill, row_number() over (PARTITION BY t.pk ORDER BY ts.skill) rn
FROM talent t, talent_skills ts
WHERE t.pk = ts.fk
)
START WITH rn = 1
CONNECT BY PRIOR pk = pk AND PRIOR rn = rn-1
GROUP BY pk, tname
ORDER BY tname;

I got the following error due to limitations on fast refresh for the Materialized view-
"ORA-12054: Cannot set the ON COMMIT refresh attribute for the materialized view"

Please can you help me with this or suggest me a better
way to go for it.

Many thanks in Advance.

Regards
Shiva

December 29, 2008 - 3:30 pm UTC

why are the business people retrieving ALL OF THE DATA - that doesn't make any sense. More information here - what are you really doing here.

connect_by_isleaf

Mihail Bratu, December 31, 2008 - 3:30 am UTC

Hi Shiva,

Try this variant:

select pk
, tname
, substr(sys_connect_by_path(skill,','), 2) skills
from (
select t.pk
, t.tname
, ts.skill
, row_number() over (partition by t.pk order by ts.skill) rn
from talent t, talent_skills ts
where t.pk = ts.fk
)
where connect_by_isleaf = 1
start with rn = 1
connect by prior pk = pk and prior rn = rn - 1
order by tname
/

Concatenate the output with materialized view

Shiva Ellur, January 21, 2009 - 4:25 am UTC

Hi Tom,

Here is the business requirement:
User is allowed to search the talents by thier skill. Based on search critiria, result should display the list of Talents and their comma separated talent skills.
For example:
Search string: Actor
And result would be:
Talent Name Talent Skills
----------- -------------
Shiva Producer,Director,Actor
Tom Manager,Actor
John Actor
Tim Actor,Director

The search result may span across more than one page and also application provides the page navigation links.

Initially I tried with your suggested SQL code as shown below:

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select pk, tname, substr(max(sys_connect_by_path(skill,',')),2) skills
2 from (
3 select t.pk, t.tname, ts.skill, row_number() over (partition by t.pk order by ts.skill) rn
4 from talent t, talent_skills ts
5 where t.pk = ts.fk
6 )
7 start with rn = 1
8 connect by prior pk = pk and prior rn = rn-1
9 where skills like '%Actor%'
10 group by pk, tname
11 order by tname
12 /

With this query, response time increased from 12 secs to 8 secs. However business users expecting result should display at the max 2 secs in a Page - 1.

Hence it drived me to create a materializd view with fast refresh method to improve the response time. However, i was unable to create the materilized view due to limitations with connect clause.

Please can you help me with this or suggest me a better
way to go for it.

Many thanks in Advance.

Regards
-Shiva

January 21, 2009 - 1:09 pm UTC

(I would really suggest you do not use MY PROMPT that makes it look like MY SQL to present your sql...)

... With this query, response time increased from 12 secs to 8 secs. ....

sounds like it decreased.

I asked you before "why are they asking for all of the data"

it appears *they are not*, they only want people that are actors.

You are building the entire hierarchy
and then pruning it

why not let's just build the hierarchy of data you are interested in?

select pk, tname, substr(max(sys_connect_by_path(skill,',')),2) skills
  from (
select t.pk, t.tname, ts.skill, row_number() over (partition by t.pk order by ts.skill) rn
  from talent t, talent_skills ts
 where t.pk = ts.fk<b>
   and t.pk in (select fk from talent_skills where skill = 'Actor')</b>
       )
start with rn = 1
connect by prior pk = pk and prior rn = rn-1
group by pk, tname
order by tname
/

find the actors
get their skills
pivot them

instead of

get everyone
find all of their skills
pivot them
and only keep the rows such that the pivoted data contains actors

Thanks a lot

Shiva, March 24, 2009 - 12:11 pm UTC

Thanks very much for valuable feedback. There was a problem with the UI design and now we made a proper change to the screen. Accordingly we also changed the SQL code to support the need.

Here is the modified SQL code:

select t.pk,stringagg(l.lookup_name) talent_skills from talent t,talent_skill ts,lookup l
where t.pk = ts.fk and
ts.skill_id=l.pk
and t.pk in (select fk from talent_skills where skill_id = ?)
group by t.pk

Thanks once again....

Using XMLAGG to aggregate causes "ORA-19011: Character string buffer too small"

Matthew, April 29, 2009 - 1:20 am UTC

Dear Tom,

I liked the solution posted by Srini from India, but found that it works sometimes, whereas other times it produces an "ORA-19011: Character string buffer too small", when I try to REPLACE the html tags with commas.

See my SQL statement below. Can you tell me if there is a way to get this REPLACE and XMLAGG solution working reliably?

Thanks,
Matthew

select c.column_name, c.full_data_type,
xmlagg(xmlelement("x",c.table_name) order by c.table_name),
replace(replace(replace(decode('a','a',xmlagg(xmlelement("x",c.table_name) order by c.table_name)),
'</x><x>',','),'</x>',null),'<x>',null) list_tables
from all_tables t,
( select i.*,
data_type||'('||
nvl(data_precision, data_length)||
decode(data_scale, null, null, ',')||
data_scale||')' full_data_type
from all_tab_columns i ) c
where c.table_name = t.table_name
group by c.column_name, c.full_data_type
having count(*) > 1
order by 1 desc;

April 29, 2009 - 9:00 am UTC

you are hitting the 4000 byte limit for strings in SQL.

Using XMLAGG to aggregate causes "ORA-19011: Character string buffer too small"

Matthew, April 30, 2009 - 1:09 am UTC

Hi Tom,

Thanks for a great site. I used to Google for Oracle SQL solutions, but now I search here first.

I don't mind the 4000 character restriction, I'm just trying to get something that will work consistently. My guess is that the return from XMLAGG sometimes implicitly converts to VARCHAR2 (<=4000) and other times CLOB (>4000), so I get an error if I assume that it's a CLOB, or a different error if I assume that it's returning a VARCHAR2.

I've searched the internet for solutions, but haven't found any.

I've added a CASE to the SQL to try and ignore values > 4000 characters. I have three alternatives for the CASE WHEN condition, see commented SQL below:
1ST TRY - length doesn't seem to handle CLOBs, get's ORA-19011
2ND TRY - dbms_lob.getlength get's ORA-06553, is it complaining that it's not a CLOB?
3RD TRY - to_clob doesn't work either, gets ORA-19011

I'm probably missing something quite simple. Can you tell me what I need to change to make this SQL run?

select c.column_name, c.full_data_type,
xmlagg(xmlelement("x",c.table_name) order by c.table_name),
-- 1ST TRY --
case when length(xmlagg(xmlelement("x",c.table_name)
order by c.table_name)) > 4000
-- 2ND TRY --
-- case when dbms_lob.getlength(xmlagg(xmlelement("x",c.table_name)
-- order by c.table_name)) > 4000
-- 3RD TRY --
-- case when dbms_lob.getlength(to_clob(xmlagg(xmlelement("x",c.table_name)
-- order by c.table_name))) > 4000
then 'TOO BIG'
else replace(replace(replace(
xmlagg(xmlelement("x",c.table_name)
order by c.table_name),
'</x><x>',','),'</x>',null),'<x>',null)
end list_tables
from all_tables t,
( select i.*,
data_type||'('||
nvl(data_precision, data_length)||
decode(data_scale, null, null, ',')||
data_scale||')' full_data_type
from all_tab_columns i ) c
where c.table_name = t.table_name
group by c.column_name, c.full_data_type
having count(*) > 1
order by 1 desc;

By the way, I'm using Oracle 10g.

Thanks again,
Matthew

April 30, 2009 - 9:47 am UTC

it is not "sometimes" varchar2 - the way it is coded, it is a varchar2.


select c.column_name, c.full_data_type,
   xmlagg(xmlelement("x",c.table_name) order by c.table_name) x,
   replace(replace(replace(decode('a','a',xmlagg(xmlelement("x", <b>TO_CLOB(c.table_name)</b> ) order by c.table_name)),
                           '</x><x>',','),'</x>',null),'<x>',null) list_tables
 from all_tables t,
    ( select i.*,
         data_type||'('||
         nvl(data_precision, data_length)||
         decode(data_scale, null, null, ',')||
         data_scale||')' full_data_type
      from all_tab_columns i ) c
 where c.table_name = t.table_name
 group by c.column_name, c.full_data_type
 having count(*) > 1
 order by 1 desc;

not saying it'll be fast or efficient, but it'll work.

Using XMLAGG to aggregate causes "ORA-19011: Character string buffer too small"

Matthew, April 30, 2009 - 8:37 pm UTC

I'm sorry Tom, your SELECT statement is still not working on my database.

To reproduce the problem using only the SYS and SYSTEM schemas, I've revised and simplified the query a little, with:
- WHERE condition on t.owner
- Removed full_data_type expression
- Shortened GROUP BY to reproduce ORA error in this simplified query.

select substr(c.column_name,1,5),
xmlagg(xmlelement("x",c.table_name) order by c.table_name) x,
replace(replace(replace(decode('a','a',xmlagg(xmlelement("x", TO_CLOB(c.table_name) )
order by c.table_name)),
'</x><x>',','),'</x>',null),'<x>',null) list_tables
from all_tables t, all_tab_columns c
where c.table_name = t.table_name
and t.owner in ('SYS','SYSTEM')
group by substr(c.column_name,1,5)
having count(*) > 1
order by 1 desc;

The above SELECT (including your TO_CLOB) still produces an ORA-19011 error. Can you see how to get past this error?

Thanks,
Matthew

April 30, 2009 - 9:46 pm UTC

I'll refer you over to otn.oracle.com -> discussion forums. I don't really use this xml stuff (don't see the point 99% of the time) - they might have an idea for you.

or use my stragg that returns a clob.

Comma separated list of table columns

Sabine, July 02, 2009 - 8:56 am UTC

I used Tom's statement above to retrieve all columns of all tables in my schema as a comma separated list. Maybe someone else finds this useful. (I use it for code generation.)

Best regards,
Sabine

WITH DATA AS (
SELECT table_name, column_name, row_number() over(PARTITION BY table_name ORDER BY column_id) rn,
       COUNT(*) over(PARTITION BY table_name) cnt
  FROM user_tab_cols
)
SELECT table_name, ltrim(sys_connect_by_path(column_name, ', '), ', ') scbp
  FROM data
 WHERE rn = cnt
 START WITH rn = 1
CONNECT BY PRIOR table_name = table_name
       AND PRIOR rn = rn - 1
 ORDER BY table_name

sing XMLAGG to aggregate causes "ORA-19011: Character string buffer too small"

Jim, July 22, 2009 - 5:50 pm UTC

Ok, so this is an ancient thread, and I just stumbled upon it.

Anyway, in response to XMLAgg-based solutions still giving string length errors, I believe the problem lies in the use of REPLACE(). You need to use a CLOB-happy equivalent.

Follow-up on XMLAgg

Jim, July 23, 2009 - 11:30 am UTC

Correcting my above ^^^

REPLACE() is fine. You just need to do REPLACE(REPLACE(XMLAgg(...).getclobval(),'<x>',''),'</x>','')) and all is well. Just ran this myself.

Error in the transpose function

Abhishek Banerjee, July 30, 2009 - 11:32 am UTC

Hello Tom,

I am the DBA for an Oracle 9i database (9.2.0.6).

There is a function which is exactly similar to the TRANSPOSE function in this article.

CREATE OR REPLACE function ISISRPT.get_transposed( p_a in varchar2 )
return varchar2
is
l_str varchar2(9000) default null;
l_sep varchar2(5) default null;
begin
for x in ( select reas_cmnt_txt from vw_reas_cmnt where reas_id = p_a ) loop
l_str := l_str || l_sep || x.reas_cmnt_txt;
l_sep := ';';
end loop;
return l_str;
end;
/

When I try to run the below query which calls the function, it fails:

ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "ISISRPT.get_transposed", line 27

Could you please help me regarding this?

Thank you,
Abhishek

August 03, 2009 - 5:28 pm UTC

what is line 27 in your code, there is no line 27 in this code

concatenate both sides

Vinod, December 16, 2009 - 4:17 pm UTC

create table chassis (chassis varchar2(20), delivery_date date);

insert into chassis values ('A', to_date('20091015', 'yyyymmdd'))
insert into chassis values ('B', to_date('20091115', 'yyyymmdd'))
insert into chassis values ('C', to_date('20091215', 'yyyymmdd'))
insert into chassis values ('D', to_date('20091002', 'yyyymmdd'))
insert into chassis values ('E', to_date('20091110', 'yyyymmdd'))
insert into chassis values ('F', to_date('20091111', 'yyyymmdd'))
insert into chassis values ('G', to_date('20091220', 'yyyymmdd'))

create table charges (charge_code_fk number(12), effective_date date, expiration_date date)

insert into charges values (100, to_date('20090101', 'yyyymmdd'), to_date('20091031', 'yyyymmdd'))
insert into charges values (200, to_date('20090101', 'yyyymmdd'), to_date('20091130', 'yyyymmdd'))
insert into charges values (300, to_date('20090101', 'yyyymmdd'), to_date('20091130', 'yyyymmdd'))
insert into charges values (400, to_date('20090101', 'yyyymmdd'), to_date('20091231', 'yyyymmdd'))
insert into charges values (500, to_date('20090101', 'yyyymmdd'), to_date('20091231', 'yyyymmdd'))
insert into charges values (600, to_date('20090101', 'yyyymmdd'), to_date('20091031', 'yyyymmdd'))

Using

with data as (
select chassis,
charge_code_fk ,
row_number() over (partition by chassis order by charge_code_fk) rn,
count(*) over (partition by chassis) cnt
from charges, chassis
where delivery_date between effective_date and expiration_date
)
select chassis, ltrim(sys_connect_by_path(charge_code_fk,','),',') charge_list
from data
where rn = cnt
start with rn = 1
connect by prior chassis = chassis and prior rn = rn-1
order by 2, 1

i can get

CHASSIS CHARGE_LIST

A 100,200,300,400,500,600
D 100,200,300,400,500,600
B 200,300,400,500
E 200,300,400,500
F 200,300,400,500
C 400,500
G 400,500

How can I get to the resultset below (group together chassis which have same charge_code_fks applicable) just using SQL?

CHASSIS_LIST CHARGE_LIST
A,D 100,200,300,400,500,600
B,E,F 200,300,400,500
C,G 400,500

ps : In actual code, charges and chassis will be PL/SQL tables.

December 17, 2009 - 7:17 am UTC

In 11gR2

ops$tkyte%ORA11GR2> select listagg(chassis,',') within group (order by chassis) chassis_list,
  2         charge_list
  3    from (
  4  with data as (
  5  select chassis,
  6         charge_code_fk  ,
  7         row_number() over (partition by chassis order by charge_code_fk) rn,
  8         count(*) over (partition by chassis) cnt
  9  from charges, chassis
 10  where delivery_date between effective_date and expiration_date
 11  )
 12  select chassis, ltrim(sys_connect_by_path(charge_code_fk,','),',') charge_list
 13    from data
 14  where rn = cnt
 15  start with rn = 1
 16  connect by prior chassis = chassis and prior rn = rn-1
 17  )
 18  group by charge_list
 19  order by 2, 1
 20  /

CHASSIS_LIST                             CHARGE_LIST
---------------------------------------- ----------------------------------------
A,D                                      100,200,300,400,500,600
B,E,F                                    200,300,400,500
C,G                                      400,500

prior to that, just use the technique you already used, just add another layer, you want to string up chassis by charge_list.

A reader, December 17, 2009 - 8:15 am UTC

Distinct is not allowed in listagg. If we need distinct then stragg is there to help us out.

get_transposed function

Helsa, April 08, 2010 - 7:30 am UTC

I've followed the example for the get_transposed function
and found it very useful.
But I still get an errormessage: ORA-06502: PL/SQL: numeric or value error: character string buffer too small.

How does the script below become when the values in the table are numbers (not varchar2)? I guess maybe this is the problem..... It fails at line 8.

Script: From table T (value a (varchar2) and value b (varchar2) I've numbers from my table and then it fails.

create or replace
function stop_no_linje( p_a in varchar2 )
return varchar2
is
l_str varchar2(2000) default null;
l_sep varchar2(1) default null;
begin
for x in ( select line_no from reisetid_pass where stop_nr= p_a ) loop
l_str := l_str || l_sep || x.line_no;
l_sep := ',';
end loop;
return l_str;
end;
/

Best regards Helsa;-)

April 13, 2010 - 8:06 am UTC

well, if l_str exceeds 2000 bytes, you'll get this

that would be my guess - not too difficult to spot a potential limit there.

It doesn't matter what x.line_no's type is - it matters what the cumulative LENGTHS are.

ops$tkyte%ORA10GR2> insert into reisetid_pass
  2  select rownum, 'a'
  3    from dual
  4  connect by level <= 527;

527 rows created.

ops$tkyte%ORA10GR2> select length(stop_no_linje('a')), stop_no_linje('a') from dual;

LENGTH(STOP_NO_LINJE('A'))
--------------------------
STOP_NO_LINJE('A')
-------------------------------------------------------------------------------
                      1999
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,3
0,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56
,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,
83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,
107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126
,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,14
6,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,1
66,167,168,169,170,171,172,173,174,175,176,177,178,179,180,181,182,183,184,185,
186,187,188,189,190,191,192,193,194,195,196,197,198,199,200,201,202,203,204,205
,206,207,208,209,210,211,212,213,214,215,216,217,218,219,220,221,222,223,224,22
5,226,227,228,229,230,231,232,233,234,235,236,237,238,239,240,241,242,243,244,2
45,246,247,248,249,250,251,252,253,254,255,256,257,258,259,260,261,262,263,264,
265,266,267,268,269,270,271,272,273,274,275,276,277,278,279,280,281,282,283,284
,285,286,287,288,289,290,291,292,293,294,295,296,297,298,299,300,301,302,303,30
4,305,306,307,308,309,310,311,312,313,314,315,316,317,318,319,320,321,322,323,3
24,325,326,327,328,329,330,331,332,333,334,335,336,337,338,339,340,341,342,343,
344,345,346,347,348,349,350,351,352,353,354,355,356,357,358,359,360,361,362,363
,364,365,366,367,368,369,370,371,372,373,374,375,376,377,378,379,380,381,382,38
3,384,385,386,387,388,389,390,391,392,393,394,395,396,397,398,399,400,401,402,4
03,404,405,406,407,408,409,410,411,412,413,414,415,416,417,418,419,420,421,422,
423,424,425,426,427,428,429,430,431,432,433,434,435,436,437,438,439,440,441,442
,443,444,445,446,447,448,449,450,451,452,453,454,455,456,457,458,459,460,461,46
2,463,464,465,466,467,468,469,470,471,472,473,474,475,476,477,478,479,480,481,4
82,483,484,485,486,487,488,489,490,491,492,493,494,495,496,497,498,499,500,501,
502,503,504,505,506,507,508,509,510,511,512,513,514,515,516,517,518,519,520,521
,522,523,524,525,526,527


ops$tkyte%ORA10GR2> insert into reisetid_pass values ( 528, 'a');

1 row created.

ops$tkyte%ORA10GR2> select length(stop_no_linje('a')), stop_no_linje('a') from dual;
select length(stop_no_linje('a')), stop_no_linje('a') from dual
              *
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "OPS$TKYTE.STOP_NO_LINJE", line 9

sachin srivastava, June 11, 2010 - 3:27 am UTC

I think this query can be useful for same purpose. \

SQL>SELECT 
RTRIM( XMLAGG( XMLELEMENT (C, category || ',') ORDER BY category
).EXTRACT ('//text()'), ','
) AS category  FROM 
(select ( select  ACA_CATEGORY_NAME from cdr.aca_category  where aca_category_id =cdr.aca_type_category_xref.aca_category_id  ) category  from cdr.aca_type_category_xref where filter_id =p_filterid

MG, September 20, 2010 - 9:39 am UTC

Hi Tom,

We have Oracle 11g STANDARD EDITION. We have some performance issues and need to install Diagnostic Pack. So my question is
(1). Is it possible to install Diagnostic Pack on 11g Standard Edition? (Because I have seen the Diagnostic Pack is for ENTERPRISE EDITION)

(2). If not possible, then how can we more easily get the stat pack info?

Thanks

September 20, 2010 - 2:58 pm UTC

1) no, it is only an option for enterprise edition.

2) you run statspack manually - you can schedule a snapshot collection using dbms_schedler, dbms_job (don't forget to use sppurge from time to time to clear it out) and run the spreport script when you want a report for a period of time.

Selective Strings Concatenation

Nadine M, October 31, 2010 - 2:26 pm UTC

Hello All,
We are migrating our MySQL Database to Oracle and we are facing the GROUP_CONCAT in built function of MySQL.
I have gone through the WM CONCAT, COLLECT AND other functions built from the Oracle aggregate packages . Our problem is this:
table A(emp_number, emp_firstname, emp_lastname, emp_status) and table B(emp_reporting_mode, emp_sub_mode, emp_sup_mode); Here emp_sup_mode represents the the employee
Those tables are not related and we have such data
Table A
emp_number emp_firstname emp_lastname emp_status
5 Raoul ESSOME STA000
6 Nadine YAYA NULL
1 Hermann LONDJI STA001
14 Honore ONANA STAT00

Table B
emp_reporting_mode emp_sup_mode emp_sub_mode
1 6 1
1 14 5
The emp_sup_mode represents the supervisor emp_number and emp_sub_mode represents the subordinate emp_number. MySQl was able to use the GROUP_CONCAT to concatenate this.
is as such and should output the emp_firstname and the emp_lastname of the supervisor where the supervisor code is the equivalence of the employee number. Here, we can see Nadine YAYA (emp_number 6) is the supervisor to Hermann LONDJI (emp_number 1) and Honore ONANA (emp_number 14) is the supervisor to Raoul ESSOME (emp_number 5).
Thanks for any input. Other methods and fuctions rather aggregate from similar id and cos.

November 01, 2010 - 3:23 pm UTC

Those tables are not related and we have such data

already starting to be confused.... especially when you later claim that emp_sub_mode is in fact apparently a foreign key to table A.

you failed to explain to me (one that does not actually use mysql) what group_concat does. that would be relevant - i have no idea what the output should be

instead of stating what you've done and failed at - how about you

a) provide proper create tables (that actually work)
b) with inserts of sample data (that also work)
c) an explanation of WHAT the output you need is - without using the word group-concat - or what you've tried and failed at - a *specification*. sample output would be *great* too - but not by itself - the words are more important than the picture in this case...

then, we might stand a chance.

Function to concatenate output

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

3 more methods

Final Question About transposing

Followup to my previous comment

Function to concatenate output

very helpful..thanks Tom.

Aggregate Function in Package

XMLTransform and XMLAgg [mis]use :-)

Please type PL/SQL in text editor.

Please type PL/SQL in text editor

Tom doesn't listen to his users

Tom's Miracle Ear

sql*plus from my point of view ...

Re: Tom's Miracle Ear

What am i getting at...

What century do you guys live?

ordering stringagg output

more ordering stringagg output

Removing Line numbers in sqlplus

Get the most recent

concat

Back to String Aggregation topic

Concat column values in one SQL SELECT statement (without PLSQL)

Concat column values in one SQL SELECT statement (without PLSQL)

Concat column values in one SQL SELECT statement

StringAgg and Parallel

Awesome

Thanks

Great info, Thanks...

Review of concatenating rows

Review of concatenating rows, part 2

Review of concatenating rows, the followup

ORA-00938: not enough arguments for function

Amendment

Another way to convert from table to comma

With 10g also SQL Model Clause may help

transpose return size > 4000

problem with NVARCHAR2 datatype

Does not work with Oracle 11

Denis himself/herself made that change

Review in the future

order by rownum desc

rownum

Why not do like this?

date with constant time

thnx

string of values

Function for cancatinating output

Concatenate multiple rows into single

Function for cancatinating output

connect_by_isleaf

Concatenate the output with materialized view

Thanks a lot

Using XMLAGG to aggregate causes "ORA-19011: Character string buffer too small"

Using XMLAGG to aggregate causes "ORA-19011: Character string buffer too small"

Using XMLAGG to aggregate causes "ORA-19011: Character string buffer too small"

Comma separated list of table columns

sing XMLAGG to aggregate causes "ORA-19011: Character string buffer too small"

Follow-up on XMLAgg

Error in the transpose function

concatenate both sides

get_transposed function

Selective Strings Concatenation

More to Explore

PL/SQL demos

PL/SQL docs