SQL & PL/SQL

drop table off_cnvctn_sttts
;
/

drop table off_cnvctns
;
/

create table off_cnvctns (
sent_id			number(10,0) not null ,
conv_cnt		number(3,0) default 0 not null ,
stat_min_mons_num	number(4,0) default 0,
stat_max_mons_num	number(4,0) default 0,
constraint offcnv_pk primary key (sent_id, conv_cnt) using index
);  
/

create table off_cnvctn_sttts
(
	sent_id		number(10,0) not null,
	statute_id	number(10,0) not null,
	conv_cnt	number(3,0) not null,
	title_code	varchar2(3 byte) not null,
	section_code	varchar2(6 byte) not null,
	subsection_code	varchar2(8 byte),
	constraint offcnvst_pk primary key (sent_id, conv_cnt, statute_id) using index,
	constraint offcnvst_offcnv_fk foreign key (sent_id, conv_cnt) references off_cnvctns (sent_id, conv_cnt)
);
/

insert into off_cnvctns (sent_id,conv_cnt,stat_min_mons_num,stat_max_mons_num) values (81,1,12,60);
insert into off_cnvctns (sent_id,conv_cnt,stat_min_mons_num,stat_max_mons_num) values (81,2,24,72);
insert into off_cnvctns (sent_id,conv_cnt,stat_min_mons_num,stat_max_mons_num) values (81,3,36,120);

insert into off_cnvctn_sttts(sent_id,statute_id,conv_cnt,title_code,section_code,subsection_code) values (81,11,1,'T11','S11','SS11');
insert into off_cnvctn_sttts(sent_id,statute_id,conv_cnt,title_code,section_code,subsection_code) values (81,12,1,'T12','S12','SS12');
insert into off_cnvctn_sttts(sent_id,statute_id,conv_cnt,title_code,section_code,subsection_code) values (81,13,1,'T13','S13','SS13');
insert into off_cnvctn_sttts(sent_id,statute_id,conv_cnt,title_code,section_code,subsection_code) values (81,21,2,'T21','S21','SS21');
insert into off_cnvctn_sttts(sent_id,statute_id,conv_cnt,title_code,section_code,subsection_code) values (81,22,2,'T22','S22','SS22');
insert into off_cnvctn_sttts(sent_id,statute_id,conv_cnt,title_code,section_code,subsection_code) values (81,31,3,'T31','S31','SS31');

select * from off_cnvctns
/

select * from off_cnvctn_sttts
/



select
	oc.conv_cnt,
	oc.stat_min_mons_num,
	oc.stat_max_mons_num,
	ocs.title_code,
	ocs.section_code,
	ocs.subsection_code
from off_cnvctns oc, off_cnvctn_sttts ocs
where oc.sent_id = ocs.sent_id
and oc.conv_cnt = ocs.conv_cnt
and oc.sent_id = 81
order by ocs.conv_cnt
;

CONV_CNT               STAT_MIN_MONS_NUM      STAT_MAX_MONS_NUM      TITLE_CODE SECTION_CODE SUBSECTION_CODE 
---------------------- ---------------------- ---------------------- ---------- ------------ --------------- 
1                      12                     60                     T11        S11          SS11            
1                      12                     60                     T12        S12          SS12            
1                      12                     60                     T13        S13          SS13            
2                      24                     72                     T21        S21          SS21            
2                      24                     72                     T22        S22          SS22            
3                      36                     120                    T31        S31          SS31            

I am trying to return rows in this form

1	12	60	T11	S11	SS11	T12	S12	SS12	T13	S13	SS13
2	24	72	T21	S21	SS21	T22	S22	SS22
3	36	120	T31	S31	SS31