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]

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]

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]

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