Tricky query

From: Alexander Miroshnikov <alexander_miroshnikov_at_hotmail.com>
Date: Fri, 31 May 2002 16:13:33 +0000 (UTC)
Message-ID: <39ab1969c3abb76dab02e3735b26928c.73752@mygate.mailgate.org>

Hi,

I am trying to write a SQL query that will aggregate values of a column of a dataset into one value that will be a comma-separated list of values of the before mentioned column.

Example:

SQL>create table test ( value varchar2(10))
SQL>/
SQL>insert into test values('1')
SQL>/
SQL>insert into test values('2')
SQL>/
SQL>insert into test values('3')
SQL>/
SQL>insert into test values('4')
SQL>/
SQL>insert into test values('5')
SQL>/
SQL>insert into test values('6')
SQL>/
SQL>insert into test values('7')
SQL>/
SQL>
SQL>select value from test
SQL>/

VALUE

---

1
2
3
4
5
6
7

7 rows selected.

I would like to write a SQL query (not a stored procedure) that will work like this:

SQL>select <tricky query> as result from test SQL>/ RESULT

---

1,2,3,4,5,6,7

1 row selected.

This query I will use to create a view for my application.

This is how far I've got -

select min( value )||decode( count(*), 0, null, 1, null,', ...') as result from test
/

RESULT

---

1, ...

1 row selected.

But it is not all I need.

Has anyone tried to solve this problem before?

Any help would be greatly appreciated.

Thanks and Regards,
Alex

-- 
Posted via Mailgate.ORG Server - http://www.Mailgate.ORG

Received on Fri May 31 2002 - 11:13:33 CDT