Re: Sql question

What'd really help here would be an aggregate version of CONCAT. Something along the lines of:

SELECT CONCAT_AGG(col1) FROM test1;

The idea being that CONCAT_AGG would return all values of col1 in a group as one, concatenated, string value.

I suppose you'd need a second arg to use in specifying the delimiter.

I'm assuming you don't know how many values will be in a group. That makes the problem harder.

I believe there may be a solution in Oracle9i Database's SYS_CONNECT_BY_PATH operator. I'm a bit too tired to really work this out tonight, but something along the lines of:

SELECT SYS_CONNECT_BY_PATH(col1,'/')
FROM test1
START WITH col1='A'
CONNECT BY col1 = CHR(ASCII(col1)+1);

You'd need to restrict the results of this query to those rows with the most delimiters, and to do that you may need to use this query as a subquery in a larger query.

I have this nagging feeling that I've written about this problem before. Or maybe I've just thought about writing about it. Well, maybe I will write about it.

Best regards,

Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:jonathan@gennick.com

Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://five.pairlist.net/mailman/listinfo/oracle-article, or send email to Oracle-article-request_at_gennick.com and include the word "subscribe" in either the subject or body.

Tuesday, October 19, 2004, 9:07:37 PM, SRIDHARAN, SAN (SBCSI) (ss4569_at_sbc.com) wrote: SSS> Here the output from the table test1

SSS> Select * from test1;

SSS> N1
SSS> ---

SSS> A
SSS> B
SSS> C
SSS> D
SSS> E
SSS> F

SSS> Is there an Oracle function or is there a Sql statement that will print SSS> the following result without us create a function.

SSS> Result
SSS> ----------- SSS> A,B,C,D,D,F

SSS> Thanks.
SSS> --
SSS> http://www.freelists.org/webpage/oracle-l

--
http://www.freelists.org/webpage/oracle-l