Forum Stats

  • 3,855,322 Users
  • 2,264,499 Discussions
  • 7,905,968 Comments

Discussions

Pivot and concatentate data

orclrunner
orclrunner Member Posts: 546
edited Apr 22, 2014 2:29PM in SQL & PL/SQL

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?

Tagged:

Best Answer

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,729 Red Diamond
    edited Apr 22, 2014 2:29PM Answer ✓

    select  f.file_id,

            f.record_id,

            f.rec_txt,

            listagg(n.note || c.comment_txt) within group(order by 1) note_comment

      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

      group by f.file_id,

               f.record_id,

               f.rec_txt

      order by f.file_id,

               f.record_id,

               f.rec_txt

    /


       FILE_ID  RECORD_ID REC_TXT NOTE_COMMENT
    ---------- ---------- ------- --------------------------------------------------------------------------------
             1          1 yadda   note1comment
             1          2 nadda   note2acomment2anote2acomment2bnote2bcomment2anote2bcomment2bnote2ccomment2anote2
                                  ccomment2b

             1          3 badda   note3comment
             1          4 sadda   note4comment
             1          5 padda   note5comment

    [email protected] >

    SY.

Answers

  • SKP
    SKP Member Posts: 844 Gold Badge
    edited Apr 22, 2014 2:29PM

    May be use the LISTAGG function

    col rec_txt for a10 
    col note for a10 
    col comment_txt for a11 
    select distinct f.file_id, f.record_id, f.rec_txt  ,listagg (n.note||c.comment_txt )within group (order by f.record_id) over(partition by f.record_id)
    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
    order by f.record_id; 
    
    1    1    yadda    note1comment
    1    2    nadda    note2acomment2anote2bcomment2bnote2acomment2bnote2bcomment2anote2ccomment2anote2ccomment2b
    1    3    badda    note3comment
    1    4    sadda    note4comment
    1    5    padda    note5comment
    
    SKP
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,729 Red Diamond
    edited Apr 22, 2014 2:29PM Answer ✓

    select  f.file_id,

            f.record_id,

            f.rec_txt,

            listagg(n.note || c.comment_txt) within group(order by 1) note_comment

      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

      group by f.file_id,

               f.record_id,

               f.rec_txt

      order by f.file_id,

               f.record_id,

               f.rec_txt

    /


       FILE_ID  RECORD_ID REC_TXT NOTE_COMMENT
    ---------- ---------- ------- --------------------------------------------------------------------------------
             1          1 yadda   note1comment
             1          2 nadda   note2acomment2anote2acomment2bnote2bcomment2anote2bcomment2bnote2ccomment2anote2
                                  ccomment2b

             1          3 badda   note3comment
             1          4 sadda   note4comment
             1          5 padda   note5comment

    [email protected] >

    SY.

This discussion has been closed.