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 |