Greetings,

I have a table with three fields

x integer,
y character(20),
z integer

Data looks like
x y z
1 field 1
1 field 2
1 field 3
2 field1 1
2 field1 2
.....

I'd like a query that in a single select could return me a concatenation of field z, semicolon delimitted, grouped by x and y, is this possible. The query output would like this for the above data:

1 field 1;2;3
2 field1 1;2

Thanks in advance for the help

Is your database XML enabled

SELECT x, y,
VARCHAR(
REPLACE(
REPLACE(
VARCHAR(
XML2CLOB(
XMLAGG(
XMLELEMENT(NAME a, z)
ORDER BY z)
),
60),
'<A>', ''),
'</A>', ';'),
60) AS z_cat
from txml group by x,y

HTH

Sathyaram

Hey, no problem! Try this (change the column names a bit, but that's easy - d1=x, d2=y, emp=z)

with x (d1, d2, cnt, list, emp, len)
as (
select d1, d2, count(*) over (partition by d1, d2),
cast(char(emp) as varchar(100)), emp, 1
from test
union all
select x.d1, x.d2, x.cnt, x.list ||','|| char(e.emp), e.emp, x.len+1
from test e, x
where e.d1 = x.d1 and e.d2 = x.d2
and e.emp > x.emp
)
select d1, d2, list
from x
where len = cnt
order by d1, d2
;

cheers,
Rob.