By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,197 Members | 974 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,197 IT Pros & Developers. It's quick & easy.

SQL to aggregate values into LIST

P: n/a
I have a table with 2 primary keys, one is a foreign key, the other is
produced by a sequence.

I want to SELECT query for one record that has a list of the
sequence-produced values for all records with a given foreign-key
value, essentially:

Create table table1 {
fk_id number,
seq_id number
}

SELECT fk_id, LIST(seq_id)
FROM table1
WHERE fk_id = 1
GROUP BY fk_id

Is there a way to do this?
Jul 19 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Ariel Jakobovits wrote:
I have a table with 2 primary keys, one is a foreign key, the other is
produced by a sequence.

I want to SELECT query for one record that has a list of the
sequence-produced values for all records with a given foreign-key
value, essentially:

Create table table1 {
fk_id number,
seq_id number
}

SELECT fk_id, LIST(seq_id)
FROM table1
WHERE fk_id = 1
GROUP BY fk_id

Is there a way to do this?


SELECT seq_id
FROM table1
WHERE fk_id = 1

Or:
SELECT fk_id, seq_id
FROM table1
GROUP BY fk_id

BTW - your table has (and can have!) only one
primary key.
--

Regards,
Frank van Bortel

Jul 19 '05 #2

P: n/a
Not exactly.
SELECT seq_id
FROM table1
WHERE fk_id = 1
RETURNS THIS:

seq_ID
------------------
1
2
3
4

Those are four separate records. I want one record, like this:

seq_id
------------------
1,2,3,4

AND THIS:
SELECT fk_id, seq_id
FROM table1
GROUP BY fk_id


RETURNS THIS:

ERROR at line 1:
ORA-00979: not a GROUP BY expression

Any more help?
Jul 19 '05 #3

P: n/a
Jan
See

"User-Defined Aggregate Functions"

in "Oracle9i Data Cartridge Developer's Guide"

http://download-west.oracle.com/docs...5/dci11agg.htm
You should create a type, e.g.:

CREATE TYPE my_agg
AS OBJECT (
string_value VARCHAR2(4000)
)

with all the 4 member functions as mentioned in docs.

You will just make some modifications, e.g. in following member function,
you would write:

---------------------
MEMBER FUNCTION ODCIAggregateIterate(
self IN OUT my_agg,
p_value IN VARCHAR2)
RETURN NUMBER IS

BEGIN
self.string_value:=
self.string_value||','||p_value;
RETURN ODCIConst.Success;

END ODCIAggregateIterate;
--------------------------
After modifications in memeber functions, create a function:

---------
CREATE OR REPLACE FUNCTION my_agg_char(p_char_value IN VARCHAR2)
RETURN VARCHAR2
PARALLEL_ENABLE AGGREGATE USING my_agg;
---------

And then, just use it:
SELECT my_agg_char(col1)
FROM my_table
Jul 19 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.