Concatenating a string [message #398965] |
Mon, 20 April 2009 01:08 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 #398982 is a reply to message #398967] |
Mon, 20 April 2009 02:31 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68675 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 #398999 is a reply to message #398986] |
Mon, 20 April 2009 04:07 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 #399045 is a reply to message #399033] |
Mon, 20 April 2009 06:18 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68675 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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
pablolee
Messages: 2882 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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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
|
|
|
|
|
|