Re: Sql question
- From: Jonathan Gennick <jonathan@xxxxxxxxxxx>
- To: "SRIDHARAN, SAN (SBCSI)" <ss4569@xxxxxxx>
- Date: Tue, 19 Oct 2004 22:01:08 -0400
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@xxxxxxxxxxx
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@xxxxxxxxxxx and
include the word "subscribe" in either the subject or body.
Tuesday, October 19, 2004, 9:07:37 PM, SRIDHARAN, SAN (SBCSI) (ss4569@xxxxxxx)
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> //www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
