Hi Tom,
create table test3
(name varchar2(10),
sub varchar2(10),
marks number
);
insert into test3 values('joe','maths',90);
insert into test3(sub,marks) values('social',80);
insert into test3(sub,marks) values('science',70);
insert into test3(sub,marks) values('english',70);
commit;
select * from test3;
NAME SUB MARKS
---------- ---------- ----------
joe maths 90
social 80
science 70
english 70
i would like to get the output as below
joe-maths:90-social:80-science:70-english:70
your help will be appreciated.
thanks
gajanana
How do you know that english, social and science belong to joe if the name column is null? What happens when you add rows for Jeff (or anyone else)?
Anyway, you're on the right track with listagg. Concatenate the columns together in the first argument. And ensure that any columns in your select not in listagg are in your group by:
create table test3
(name varchar2(10),
sub varchar2(10),
marks number
);
insert into test3 values('joe','maths',90);
insert into test3(sub,marks) values('social',80);
insert into test3(sub,marks) values('science',70);
insert into test3(sub,marks) values('english',70);
commit;
select listagg(decode(name, null, null, name || '-') || sub || ':' || marks,'-')
within group (order by name, sub)
from test3;
LISTAGG(DECODE(NAME,NULL,NULL,NAME||'-')||SUB||':'||MARKS,'-')WITHINGROUP(ORDERBYNAME,SUB)
joe-maths:90-english:70-science:70-social:80