Oracle 11g Release 2

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

create table table1(app, language, description) as (
    select 195, 12, 'Involved person' from dual union all
    select 195, 27, 'Involved person' from dual union all   
    select 196,  1, 'Involvert legemiddel' from dual union all
    select 196,  2, 'Involved drug' from dual union all
    select 196,  3, 'Involverad lakemedel' from dual union all
    select 196,  4, 'Involveret legemiddel' from dual union all
    select 196, 12, 'Involved drug' from dual union all
    select 196, 27, 'Involved drug' from dual union all
    select 197,  1, 'Eksponeringsverdier' from dual union all
    select 197,  2, 'Exposure values' from dual union all
    select 197,  3, 'Exponeringsvarden' from dual union all
    select 197,  4, 'Eksponeringsv?rdier' from dual union all
    select 197, 12, 'Exposure values' from dual union all
    select 197, 27, 'Exposure values' from dual union all
    select 198,  1, 'Indikatorer' from dual union all
    select 198,  2, 'Indicators' from dual union all
    select 198,  3, 'Indikatorer' from dual union all
    select 198,  4, 'Indikatorer' from dual union all
    select 198, 12, 'Indicators' from dual union all
    select 198, 27, 'Indicators' from dual union all
    select 199,  1, 'Generell klassifisering' from dual union all
    select 199,  2, 'General classification' from dual union all
    select 199,  3, 'Generell klassificering' from dual union all
    select 199,  4, 'Generel klassifisering' from dual union all
    select 199, 12, 'General classification' from dual union all
    select 199, 27, 'General classification' from dual );
26 rows affected

create table table2(trans, app) as (
    select  1, 195 from dual union all
    select  2, 195 from dual union all
    select  3, 196 from dual union all
    select  4, 196 from dual union all
    select  5, 196 from dual union all
    select  6, 196 from dual union all
    select  7, 196 from dual union all
    select  8, 196 from dual union all
    select  9, 197 from dual union all
    select 10, 197 from dual union all
    select 11, 197 from dual union all
    select 12, 197 from dual union all
    select 13, 197 from dual union all
    select 14, 197 from dual union all
    select 15, 198 from dual union all
    select 16, 198 from dual union all
    select 17, 198 from dual union all
    select 18, 198 from dual union all
    select 19, 198 from dual union all
    select 20, 198 from dual union all
    select 21, 199 from dual union all
    select 22, 199 from dual union all
    select 23, 199 from dual union all
    select 24, 199 from dual union all
    select 25, 199 from dual union all
    select 26, 199 from dual );
26 rows affected

SELECT      rtrim(xmlagg(XMLELEMENT(e,table1.DESCRIPTION,',').EXTRACT  ('//text()')
                  ).GetClobVal(),',') 
        FROM table1

     left  join table2 on  table1.app = table2.app
     AND LANGUAGE = 2
       GROUP BY table2.app
     


RTRIM(XMLAGG(XMLELEMENT(E,TABLE1.DESCRIPTION,',').EXTRACT('//TEXT()')).GETCLOBVAL(),',')


Involved drug,Involved drug,Involved drug,Involved drug,Involved drug,Involved drug

Exposure values,Exposure values,Exposure values,Exposure values,Exposure values,Exposure values

Indicators,Indicators,Indicators,Indicators,Indicators,Indicators

General classification,General classification,General classification,General classification,General classification,General classification

Involved drug,Indicators,Indikatorer,Generel klassifisering,General classification,General classification,Exponeringsvarden,Eksponeringsv?rdier,Generell klassifisering,Eksponeringsverdier,Involveret legemiddel,Involved drug,Involved person,Indikatorer,Indikatorer,Exposure values,Exposure values,Involvert legemiddel,Indicators,Involved person,Generell klassificering,Involverad lakemedel


SELECT   nvl(max(case when language = 2 then description end), 'NULL') key_event,   rtrim(xmlagg(XMLELEMENT(e,table1.DESCRIPTION,',').EXTRACT  ('//text()')
                  ).GetClobVal(),',') 
        FROM table1

     left  join table2 on  table1.app = table2.app
     AND LANGUAGE = 2
       GROUP BY table2.app


KEY_EVENT
RTRIM(XMLAGG(XMLELEMENT(E,TABLE1.DESCRIPTION,',').EXTRACT('//TEXT()')).GETCLOBVAL(),',')


Involved drug
Involved drug,Involved drug,Involved drug,Involved drug,Involved drug,Involved drug

Exposure values
Exposure values,Exposure values,Exposure values,Exposure values,Exposure values,Exposure values

Indicators
Indicators,Indicators,Indicators,Indicators,Indicators,Indicators

General classification
General classification,General classification,General classification,General classification,General classification,General classification

NULL
Involved drug,Indicators,Indikatorer,Generel klassifisering,General classification,General classification,Exponeringsvarden,Eksponeringsv?rdier,Generell klassifisering,Eksponeringsverdier,Involveret legemiddel,Involved drug,Involved person,Indikatorer,Indikatorer,Exposure values,Exposure values,Involvert legemiddel,Indicators,Involved person,Generell klassificering,Involverad lakemedel

KEY_EVENT	RTRIM(XMLAGG(XMLELEMENT(E,TABLE1.DESCRIPTION,',').EXTRACT('//TEXT()')).GETCLOBVAL(),',')
Involved drug	Involved drug,Involved drug,Involved drug,Involved drug,Involved drug,Involved drug
Exposure values	Exposure values,Exposure values,Exposure values,Exposure values,Exposure values,Exposure values
Indicators	Indicators,Indicators,Indicators,Indicators,Indicators,Indicators
General classification	General classification,General classification,General classification,General classification,General classification,General classification
NULL	Involved drug,Indicators,Indikatorer,Generel klassifisering,General classification,General classification,Exponeringsvarden,Eksponeringsv?rdier,Generell klassifisering,Eksponeringsverdier,Involveret legemiddel,Involved drug,Involved person,Indikatorer,Indikatorer,Exposure values,Exposure values,Involvert legemiddel,Indicators,Involved person,Generell klassificering,Involverad lakemedel