Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE  TABLE tmp_main AS (
  SELECT rownum col1, 'txt_' || to_char(rownum) Col2 FROM dual 
    CONNECT BY rownum<=2
);
2 rows affected

CREATE TABLE tmp_keys AS (
  SELECT DECODE(rownum,1,1,2,1,3,1,4,2,5,2,6,2) col1, 'key_' || to_char(rownum) key1 , rownum seq FROM dual 
     CONNECT BY rownum<=6
);
6 rows affected

CREATE TABLE tmp_line AS (
  SELECT DECODE(rownum,1,1,2,1,3,1,4,1,5,2,6,2,7,2,8,2) col1, 'line_' || DECODE(rownum,2,1,3,1,4,2,5,3,7,3,8,4) line1 , rownum seq   FROM dual 
     CONNECT BY rownum<=8
);
8 rows affected

update tmp_line set line1=null where line1='line_';
2 rows affected

SELECT  m.col1,
        m.col2,
        SUBSTR( k.key_list, 1, LENGTH( k.key_list ) - 1 ) AS key_list,
        SUBSTR( l.line_list, 1, LENGTH( l.line_list ) - 1 ) AS line_list
FROM    tmp_main m
        JOIN (
          SELECT col1,
                 XMLAGG(
                   XMLELEMENT(E,key1 , ',').EXTRACT('//text()')
                   ORDER BY seq
                 ).GetClobVal() as key_list
          FROM   (
            SELECT k.*,
                   ROW_NUMBER() OVER ( PARTITION BY col1, key1 ORDER BY seq ) AS rn
            FROM   tmp_keys k
          )
          WHERE  rn = 1
          GROUP BY col1
        ) k
        ON m.col1=k.col1
        JOIN (
          SELECT col1,
                 XMLAGG(
                   XMLELEMENT(E,line1 , ',').EXTRACT('//text()')
                   ORDER BY seq
                 ).GetClobVal() as line_list
          FROM   (
            SELECT l.*,
                   ROW_NUMBER() OVER ( PARTITION BY col1, line1 ORDER BY seq ) AS rn
            FROM   tmp_line l
          )
          WHERE  rn = 1
          GROUP BY col1
        ) l
        ON m.col1=l.col1;


COL1
COL2
KEY_LIST
LINE_LIST


1
txt_1
key_1,key_2,key_3
,line_1,line_2

2
txt_2
key_4,key_5,key_6
line_3,,line_4

COL1	COL2	KEY_LIST	LINE_LIST
1	txt_1	key_1,key_2,key_3	,line_1,line_2
2	txt_2	key_4,key_5,key_6	line_3,,line_4