Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, GAJANANA.

Asked: May 04, 2017 - 10:32 pm UTC

Last updated: May 05, 2017 - 10:42 am UTC

Version: 11.1.0.6.0

Viewed 1000+ times

You Asked

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

with LiveSQL Test Case:

and Chris said...

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 

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

rows concate

GAJANANA GANJIGATTI, May 05, 2017 - 9:49 am UTC

Hi Tom,

Thank you very much for the help.
Chris Saxon
May 05, 2017 - 10:42 am UTC

It's Chris here, but thanks anyway! ;)