Home » Developer & Programmer » Reports & Discoverer » Concatenating a string (10g)
Concatenating a string [message #398965] Mon, 20 April 2009 01:08 Go to next message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
Hi,

This is my test case


create table part_coments (
  id number,
  comments varchar2(2000),
  creation_date date);
  
insert into part_coments values(1,'abc1','1-Jul-2008');
insert into part_coments values(1,'abc2','2-Jul-2008');
insert into part_coments values(1,'abc3','3-Jul-2008');
insert into part_coments values(2,'abc','1-Jul-2008');

commit;


Is there any way i could output the following

id , comments
1    abc1. abc2. abc3
2    abc


I tried using a function to achieve this by passing in id and having a cursor to loop through the records and concatenating 'comments' field, but is there any way i could achieve this purely in sql?
Re: Concatenating a string [message #398967 is a reply to message #398965] Mon, 20 April 2009 01:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Search for PIVOT.
This question is asked EVERY WEEK, so please search BEFORE posting.

Regards
Michel
Re: Concatenating a string [message #398982 is a reply to message #398967] Mon, 20 April 2009 02:31 Go to previous messageGo to next message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
I found the following solution after searching for 'pivot'. The query is running for some amount of time to return results. Is this using the 'standard pivot' approach?

SELECT	id,
   	     MAX (SUBSTR (SYS_CONNECT_BY_PATH (comments, '/'), 2)) AS comments
   FROM    (SELECT id, comments,
   		     ROW_NUMBER () OVER (PARTITION BY id ORDER BY ROWID) AS rn
   	      FROM    part_coments)
   GROUP   BY id
   START   WITH rn = 1
   CONNECT BY PRIOR id = id AND PRIOR rn = rn - 1
   ORDER   BY id
Re: Concatenating a string [message #398983 is a reply to message #398982] Mon, 20 April 2009 02:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This one should be a little bit faster:
SELECT	id,
   	     SUBSTR (SYS_CONNECT_BY_PATH (comments, '/'), 2) AS comments
   FROM    (SELECT id, comments,
   		     ROW_NUMBER () OVER (PARTITION BY id ORDER BY NULL) AS rn,
                     COUNT(*) OVER (PARTITION BY id) cnt
   	      FROM    part_coments)
   WHERE rn = cnt
   START   WITH rn = 1
   CONNECT BY PRIOR id = id AND PRIOR rn = rn - 1
   ORDER   BY id

This is a standard query, there are many other solutions, search for STRAGG, WM_CONCAT, "rows to column"...

Regards
Michel
Re: Concatenating a string [message #398985 is a reply to message #398983] Mon, 20 April 2009 02:57 Go to previous messageGo to next message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
thank you.

do you have an idea why theres a substr function being used?
Re: Concatenating a string [message #398986 is a reply to message #398985] Mon, 20 April 2009 03:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just try it without substr and you will see its purpose.
Or read SYS_CONNECT_BY_PATH documentation.

Regards
Michel

[Updated on: Mon, 20 April 2009 03:03]

Report message to a moderator

Re: Concatenating a string [message #398999 is a reply to message #398986] Mon, 20 April 2009 04:07 Go to previous messageGo to next message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
I was going through it, wm_concat is quite useful, but undocumented

I also cant add the character chr(10). Is there any workaround for this


SELECT	id,
   	     SUBSTR (SYS_CONNECT_BY_PATH (comments, CHR(10)), 2) AS comments
   FROM    (SELECT id, comments,
   		     ROW_NUMBER () OVER (PARTITION BY id ORDER BY NULL) AS rn,
                     COUNT(*) OVER (PARTITION BY id) cnt
   	      FROM    part_coments)
   WHERE rn = cnt
   START   WITH rn = 1
   CONNECT BY PRIOR id = id AND PRIOR rn = rn - 1
Re: Concatenating a string [message #399000 is a reply to message #398999] Mon, 20 April 2009 04:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
workaround to what? What do you want to achieve? Why there is this chr(10) in your query?

Regards
Michel
Re: Concatenating a string [message #399033 is a reply to message #399000] Mon, 20 April 2009 05:57 Go to previous messageGo to next message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
apologies for not clearly stating.

I want the separator between fields to be a line feed (chr(10)) instead of a '/' or ',' or any other separator.
Re: Concatenating a string [message #399045 is a reply to message #399033] Mon, 20 April 2009 06:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you want a line feed as a separator, are you sure you need to concatenate the values?
What is your client application (SQL*Plus...)?
What do you try to achieve, in the end?

You can use REPLACE to replace the separator character to new line.

Regards
Michel

[Updated on: Mon, 20 April 2009 06:19]

Report message to a moderator

Re: Concatenating a string [message #399052 is a reply to message #399045] Mon, 20 April 2009 06:31 Go to previous messageGo to next message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
i am using oracle discoverer, and this line feed will be used to to separate the comments field. You might have multiple comments added to an issue, so want to display those multiple comments in new line all in a single field.

thank you
Re: Concatenating a string [message #399053 is a reply to message #399052] Mon, 20 April 2009 06:36 Go to previous messageGo to next message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
ajitpal.s wrote on Mon, 20 April 2009 12:31
i am using oracle discoverer, and this line feed will be used to to separate the comments field. You might have multiple comments added to an issue, so want to display those multiple comments in new line all in a single field.

thank you

Why concatenate them together? You are taking values that appear on separate lines, performing a costly process to get them onto a single line, then splitting them out onto ... wait for it .... separate lines Sound like an act of folly to me.
Re: Concatenating a string [message #399054 is a reply to message #399053] Mon, 20 April 2009 06:42 Go to previous messageGo to next message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
actually this is how it goes.

we have an issue no, and comments, and the creation date of the comments. so data might be as below. The lines below is generated by the third party software

issueno | comments | creationdate
1       | some comments | 1-Jan-2008
1       | some more comments | 1-Jan-2008


So now to make it easily viewable, user wants all this to be display in single column grouped by issueno, like below
issueno | comments
1       | some comments <line feed or chr(10)>
        | some more comments
Re: Concatenating a string [message #399055 is a reply to message #399054] Mon, 20 April 2009 06:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So you are trying to solve an application display issue with an inefficient SQL statement.
Not really wise.

Regards
Michel
Re: Concatenating a string [message #399057 is a reply to message #399054] Mon, 20 April 2009 06:59 Go to previous messageGo to next message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
This functionality is already available in Discoverer. I haven't used it in a long time, but from memory, you can use the sorting wizard to do this. Just apply a group sort on the issueno column.
See how much easier it is if you actually tell us what it is you're trying to do. It would have been even easier if you had gone to the Discoverer forum Wink
Re: Concatenating a string [message #399058 is a reply to message #399057] Mon, 20 April 2009 07:07 Go to previous message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
hmmm, Smile I was not aware of it, will check that feature out.

thanks all for input
Previous Topic: fixed positin item
Next Topic: Quarterly Totals
Goto Forum:
  


Current Time: Thu Dec 08 20:22:17 CST 2016

Total time taken to generate the page: 0.12510 seconds