Oracle 11g Release 2
I have three tables: file_data, notes and comments. A one-to-many relations exists between file_data and the other two tables.
Primary keys in all tables: file_id and record_id.
I need to pivot notes.notes and comments.comment_txt and then concatenate them into a single-string.
Here is the setup:
create table file_data
( file_id number
,record_id number
,rec_txt varchar2(255)
)
/
insert into file_data values(1,1,'yadda')
/
insert into file_data values(1,2,'nadda')
/
insert into file_data values(1,3,'badda')
/
insert into file_data values(1,4,'sadda')
/
insert into file_data values(1,5,'padda')
/
create table notes
( file_id number
,record_id number
,note varchar2(255)
)
/
insert into notes values(1,1,'note1')
/
insert into notes values(1,2,'note2a')
/
insert into notes values(1,2,'note2b')
/
insert into notes values(1,2,'note2c')
/
insert into notes values(1,3,'note3')
/
insert into notes values(1,4,'note4')
/
insert into notes values(1,5,'note5')
/
create table comments
( file_id number
,record_id number
,comment_txt varchar2(255)
)
/
insert into comments values(1,1,'comment')
/
insert into comments values(1,2,'comment2a')
/
insert into comments values(1,2,'comment2b')
/
insert into comments values(1,3,'comment')
/
insert into comments values(1,4,'comment')
/
insert into comments values(1,5,'comment')
/
commit;
col rec_txt for a10
col note for a10
col comment_txt for a11
select f.file_id, f.record_id, f.rec_txt,n.note,c.comment_txt
from file_data f, notes n, comments c
where f.file_id = 1
and f.file_id = n.file_id
and f.record_id = n.record_id
and f.file_id = c.file_id
and f.record_id = c.record_id ;
FILE_ID RECORD_ID REC_TXT NOTE COMMENT_TXT
---------- ---------- ---------- ---------- -----------
1 1 yadda note1 comment
1 2 nadda note2a comment2b
1 2 nadda note2a comment2a
1 2 nadda note2b comment2b
1 2 nadda note2b comment2a
1 2 nadda note2c comment2b
1 2 nadda note2c comment2a
1 3 badda note3 comment
1 4 sadda note4 comment
1 5 padda note5 comment
Here is the desired output:
FILE_ID RECORD_ID REC_TXT NOTE_COMMENT
---------- ---------- ---------- ---------------------
1 1 yadda NOTE1COMMENT
1 2 nadda NOTE2ACOMMENT2BNOTE2ACOMMENT2ANOTE2BCOMMENT2BNOTE2BCOMMENT2ANOTE2CCOMMENT2BNOTE2CCOMMENT2A
1 3 badda NOTE3COMMENT
1 4 sadda NOTE4COMMENT
1 5 padda NOTE5COMMENT
The final output needs to be trimmed and uppercase.The solution can be SQL or PL/SQL. Any thoughts on how to accomplish this?