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 trans, t2.app, 
       nvl(max(case when language = 2 then description end), 'NULL') key_event,
       listagg(description, ', ') within group (order by t1.language) list
  from table2 t2 
  left join table1 t1 on t1.app = t2.app 
  group by trans, t2.app
  order by trans;


TRANS
APP
KEY_EVENT
LIST


1
195
NULL
Involved person, Involved person

2
195
NULL
Involved person, Involved person

3
196
Involved drug
Involvert legemiddel, Involved drug, Involverad lakemedel, Involveret legemiddel, Involved drug, Involved drug

4
196
Involved drug
Involvert legemiddel, Involved drug, Involverad lakemedel, Involveret legemiddel, Involved drug, Involved drug

5
196
Involved drug
Involvert legemiddel, Involved drug, Involverad lakemedel, Involveret legemiddel, Involved drug, Involved drug

6
196
Involved drug
Involvert legemiddel, Involved drug, Involverad lakemedel, Involveret legemiddel, Involved drug, Involved drug

7
196
Involved drug
Involvert legemiddel, Involved drug, Involverad lakemedel, Involveret legemiddel, Involved drug, Involved drug

8
196
Involved drug
Involvert legemiddel, Involved drug, Involverad lakemedel, Involveret legemiddel, Involved drug, Involved drug

9
197
Exposure values
Eksponeringsverdier, Exposure values, Exponeringsvarden, Eksponeringsv?rdier, Exposure values, Exposure values

10
197
Exposure values
Eksponeringsverdier, Exposure values, Exponeringsvarden, Eksponeringsv?rdier, Exposure values, Exposure values

11
197
Exposure values
Eksponeringsverdier, Exposure values, Exponeringsvarden, Eksponeringsv?rdier, Exposure values, Exposure values

12
197
Exposure values
Eksponeringsverdier, Exposure values, Exponeringsvarden, Eksponeringsv?rdier, Exposure values, Exposure values

13
197
Exposure values
Eksponeringsverdier, Exposure values, Exponeringsvarden, Eksponeringsv?rdier, Exposure values, Exposure values

14
197
Exposure values
Eksponeringsverdier, Exposure values, Exponeringsvarden, Eksponeringsv?rdier, Exposure values, Exposure values

15
198
Indicators
Indikatorer, Indicators, Indikatorer, Indikatorer, Indicators, Indicators

16
198
Indicators
Indikatorer, Indicators, Indikatorer, Indikatorer, Indicators, Indicators

17
198
Indicators
Indikatorer, Indicators, Indikatorer, Indikatorer, Indicators, Indicators

18
198
Indicators
Indikatorer, Indicators, Indikatorer, Indikatorer, Indicators, Indicators

19
198
Indicators
Indikatorer, Indicators, Indikatorer, Indikatorer, Indicators, Indicators

20
198
Indicators
Indikatorer, Indicators, Indikatorer, Indikatorer, Indicators, Indicators

21
199
General classification
Generell klassifisering, General classification, Generell klassificering, Generel klassifisering, General classification, General classification

22
199
General classification
Generell klassifisering, General classification, Generell klassificering, Generel klassifisering, General classification, General classification

23
199
General classification
Generell klassifisering, General classification, Generell klassificering, Generel klassifisering, General classification, General classification

24
199
General classification
Generell klassifisering, General classification, Generell klassificering, Generel klassifisering, General classification, General classification

25
199
General classification
Generell klassifisering, General classification, Generell klassificering, Generel klassifisering, General classification, General classification

26
199
General classification
Generell klassifisering, General classification, Generell klassificering, Generel klassifisering, General classification, General classification


select trans, t2.app,  
       coalesce(max(case when language =  2 then description end), 
                max(case when language = 12 then description end), 
                max(case when language = 27 then description end), 
               'NULL') key_event,
       listagg(description, ', ') within group (order by t1.language) list
  from table2 t2 
  left join table1 t1 on t1.app = t2.app 
  group by trans, t2.app
  order by trans;


TRANS
APP
KEY_EVENT
LIST


1
195
Involved person
Involved person, Involved person

2
195
Involved person
Involved person, Involved person

3
196
Involved drug
Involvert legemiddel, Involved drug, Involverad lakemedel, Involveret legemiddel, Involved drug, Involved drug

4
196
Involved drug
Involvert legemiddel, Involved drug, Involverad lakemedel, Involveret legemiddel, Involved drug, Involved drug

5
196
Involved drug
Involvert legemiddel, Involved drug, Involverad lakemedel, Involveret legemiddel, Involved drug, Involved drug

6
196
Involved drug
Involvert legemiddel, Involved drug, Involverad lakemedel, Involveret legemiddel, Involved drug, Involved drug

7
196
Involved drug
Involvert legemiddel, Involved drug, Involverad lakemedel, Involveret legemiddel, Involved drug, Involved drug

8
196
Involved drug
Involvert legemiddel, Involved drug, Involverad lakemedel, Involveret legemiddel, Involved drug, Involved drug

9
197
Exposure values
Eksponeringsverdier, Exposure values, Exponeringsvarden, Eksponeringsv?rdier, Exposure values, Exposure values

10
197
Exposure values
Eksponeringsverdier, Exposure values, Exponeringsvarden, Eksponeringsv?rdier, Exposure values, Exposure values

11
197
Exposure values
Eksponeringsverdier, Exposure values, Exponeringsvarden, Eksponeringsv?rdier, Exposure values, Exposure values

12
197
Exposure values
Eksponeringsverdier, Exposure values, Exponeringsvarden, Eksponeringsv?rdier, Exposure values, Exposure values

13
197
Exposure values
Eksponeringsverdier, Exposure values, Exponeringsvarden, Eksponeringsv?rdier, Exposure values, Exposure values

14
197
Exposure values
Eksponeringsverdier, Exposure values, Exponeringsvarden, Eksponeringsv?rdier, Exposure values, Exposure values

15
198
Indicators
Indikatorer, Indicators, Indikatorer, Indikatorer, Indicators, Indicators

16
198
Indicators
Indikatorer, Indicators, Indikatorer, Indikatorer, Indicators, Indicators

17
198
Indicators
Indikatorer, Indicators, Indikatorer, Indikatorer, Indicators, Indicators

18
198
Indicators
Indikatorer, Indicators, Indikatorer, Indikatorer, Indicators, Indicators

19
198
Indicators
Indikatorer, Indicators, Indikatorer, Indikatorer, Indicators, Indicators

20
198
Indicators
Indikatorer, Indicators, Indikatorer, Indikatorer, Indicators, Indicators

21
199
General classification
Generell klassifisering, General classification, Generell klassificering, Generel klassifisering, General classification, General classification

22
199
General classification
Generell klassifisering, General classification, Generell klassificering, Generel klassifisering, General classification, General classification

23
199
General classification
Generell klassifisering, General classification, Generell klassificering, Generel klassifisering, General classification, General classification

24
199
General classification
Generell klassifisering, General classification, Generell klassificering, Generel klassifisering, General classification, General classification

25
199
General classification
Generell klassifisering, General classification, Generell klassificering, Generel klassifisering, General classification, General classification

26
199
General classification
Generell klassifisering, General classification, Generell klassificering, Generel klassifisering, General classification, General classification

TRANS	APP	KEY_EVENT	LIST
1	195	NULL	Involved person, Involved person
2	195	NULL	Involved person, Involved person
3	196	Involved drug	Involvert legemiddel, Involved drug, Involverad lakemedel, Involveret legemiddel, Involved drug, Involved drug
4	196	Involved drug	Involvert legemiddel, Involved drug, Involverad lakemedel, Involveret legemiddel, Involved drug, Involved drug
5	196	Involved drug	Involvert legemiddel, Involved drug, Involverad lakemedel, Involveret legemiddel, Involved drug, Involved drug
6	196	Involved drug	Involvert legemiddel, Involved drug, Involverad lakemedel, Involveret legemiddel, Involved drug, Involved drug
7	196	Involved drug	Involvert legemiddel, Involved drug, Involverad lakemedel, Involveret legemiddel, Involved drug, Involved drug
8	196	Involved drug	Involvert legemiddel, Involved drug, Involverad lakemedel, Involveret legemiddel, Involved drug, Involved drug
9	197	Exposure values	Eksponeringsverdier, Exposure values, Exponeringsvarden, Eksponeringsv?rdier, Exposure values, Exposure values
10	197	Exposure values	Eksponeringsverdier, Exposure values, Exponeringsvarden, Eksponeringsv?rdier, Exposure values, Exposure values
11	197	Exposure values	Eksponeringsverdier, Exposure values, Exponeringsvarden, Eksponeringsv?rdier, Exposure values, Exposure values
12	197	Exposure values	Eksponeringsverdier, Exposure values, Exponeringsvarden, Eksponeringsv?rdier, Exposure values, Exposure values
13	197	Exposure values	Eksponeringsverdier, Exposure values, Exponeringsvarden, Eksponeringsv?rdier, Exposure values, Exposure values
14	197	Exposure values	Eksponeringsverdier, Exposure values, Exponeringsvarden, Eksponeringsv?rdier, Exposure values, Exposure values
15	198	Indicators	Indikatorer, Indicators, Indikatorer, Indikatorer, Indicators, Indicators
16	198	Indicators	Indikatorer, Indicators, Indikatorer, Indikatorer, Indicators, Indicators
17	198	Indicators	Indikatorer, Indicators, Indikatorer, Indikatorer, Indicators, Indicators
18	198	Indicators	Indikatorer, Indicators, Indikatorer, Indikatorer, Indicators, Indicators
19	198	Indicators	Indikatorer, Indicators, Indikatorer, Indikatorer, Indicators, Indicators
20	198	Indicators	Indikatorer, Indicators, Indikatorer, Indikatorer, Indicators, Indicators
21	199	General classification	Generell klassifisering, General classification, Generell klassificering, Generel klassifisering, General classification, General classification
22	199	General classification	Generell klassifisering, General classification, Generell klassificering, Generel klassifisering, General classification, General classification
23	199	General classification	Generell klassifisering, General classification, Generell klassificering, Generel klassifisering, General classification, General classification
24	199	General classification	Generell klassifisering, General classification, Generell klassificering, Generel klassifisering, General classification, General classification
25	199	General classification	Generell klassifisering, General classification, Generell klassificering, Generel klassifisering, General classification, General classification
26	199	General classification	Generell klassifisering, General classification, Generell klassificering, Generel klassifisering, General classification, General classification

TRANS	APP	KEY_EVENT	LIST
1	195	Involved person	Involved person, Involved person
2	195	Involved person	Involved person, Involved person
3	196	Involved drug	Involvert legemiddel, Involved drug, Involverad lakemedel, Involveret legemiddel, Involved drug, Involved drug
4	196	Involved drug	Involvert legemiddel, Involved drug, Involverad lakemedel, Involveret legemiddel, Involved drug, Involved drug
5	196	Involved drug	Involvert legemiddel, Involved drug, Involverad lakemedel, Involveret legemiddel, Involved drug, Involved drug
6	196	Involved drug	Involvert legemiddel, Involved drug, Involverad lakemedel, Involveret legemiddel, Involved drug, Involved drug
7	196	Involved drug	Involvert legemiddel, Involved drug, Involverad lakemedel, Involveret legemiddel, Involved drug, Involved drug
8	196	Involved drug	Involvert legemiddel, Involved drug, Involverad lakemedel, Involveret legemiddel, Involved drug, Involved drug
9	197	Exposure values	Eksponeringsverdier, Exposure values, Exponeringsvarden, Eksponeringsv?rdier, Exposure values, Exposure values
10	197	Exposure values	Eksponeringsverdier, Exposure values, Exponeringsvarden, Eksponeringsv?rdier, Exposure values, Exposure values
11	197	Exposure values	Eksponeringsverdier, Exposure values, Exponeringsvarden, Eksponeringsv?rdier, Exposure values, Exposure values
12	197	Exposure values	Eksponeringsverdier, Exposure values, Exponeringsvarden, Eksponeringsv?rdier, Exposure values, Exposure values
13	197	Exposure values	Eksponeringsverdier, Exposure values, Exponeringsvarden, Eksponeringsv?rdier, Exposure values, Exposure values
14	197	Exposure values	Eksponeringsverdier, Exposure values, Exponeringsvarden, Eksponeringsv?rdier, Exposure values, Exposure values
15	198	Indicators	Indikatorer, Indicators, Indikatorer, Indikatorer, Indicators, Indicators
16	198	Indicators	Indikatorer, Indicators, Indikatorer, Indikatorer, Indicators, Indicators
17	198	Indicators	Indikatorer, Indicators, Indikatorer, Indikatorer, Indicators, Indicators
18	198	Indicators	Indikatorer, Indicators, Indikatorer, Indikatorer, Indicators, Indicators
19	198	Indicators	Indikatorer, Indicators, Indikatorer, Indikatorer, Indicators, Indicators
20	198	Indicators	Indikatorer, Indicators, Indikatorer, Indikatorer, Indicators, Indicators
21	199	General classification	Generell klassifisering, General classification, Generell klassificering, Generel klassifisering, General classification, General classification
22	199	General classification	Generell klassifisering, General classification, Generell klassificering, Generel klassifisering, General classification, General classification
23	199	General classification	Generell klassifisering, General classification, Generell klassificering, Generel klassifisering, General classification, General classification
24	199	General classification	Generell klassifisering, General classification, Generell klassificering, Generel klassifisering, General classification, General classification
25	199	General classification	Generell klassifisering, General classification, Generell klassificering, Generel klassifisering, General classification, General classification
26	199	General classification	Generell klassifisering, General classification, Generell klassificering, Generel klassifisering, General classification, General classification