SQL to aggregate values into LIST

[Ariel Jakobovits]

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?

[Frank van Bortel]

Re: SQL to aggregate values into LIST

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

[Ariel Jakobovits]

Re: SQL to aggregate values into LIST

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?

[Jan]

Re: SQL to aggregate values into LIST

See

"User-Defined Aggregate Functions"

in "Oracle9i Data Cartridge Developer's Guide"

http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96595/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 ODCIAggregateIt erate(
self IN OUT my_agg,
p_value IN VARCHAR2)
RETURN NUMBER IS

BEGIN
self.string_val ue:=
self.string_val ue||','||p_valu e;
RETURN ODCIConst.Succe ss;

END ODCIAggregateIt erate;
--------------------------


After modifications in memeber functions, create a function:

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

And then, just use it:


SELECT my_agg_char(col 1)
FROM my_table