Oracle 18c

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

create table your_table (id, salutation, city, state, acnt) as 
select '01', 'Ms.', 'Delray Beach', 'FL', null from dual
union all
select '02', 'Mrs.', 'Lauderhill', 'FL', 'DCT' from dual
union all
select '03', 'Ms.', 'New York', 'NY', null from dual
union all
select '04', 'Ms.', 'Chicago', 'IL', 'ACT' from dual
union all
select '05', null, 'Chicago', 'IL', 'ACT' from dual
5 rows affected

select id, salutation, city, state, acnt, tag
from your_table
cross apply (
  select case when city = 'Chicago' then 'Metro' end as tag from dual
  union all
  select case when salutation in ('Ms.', 'Mrs.') then 'Greeting' end as tag from dual
  union all
  select case when acnt = 'ACT' then 'Mailing' end as tag from dual
)


ID
SALUTATION
CITY
STATE
ACNT
TAG


01
Ms.
Delray Beach
FL
null
null

01
Ms.
Delray Beach
FL
null
Greeting

01
Ms.
Delray Beach
FL
null
null

02
Mrs.
Lauderhill
FL
DCT
null

02
Mrs.
Lauderhill
FL
DCT
Greeting

02
Mrs.
Lauderhill
FL
DCT
null

03
Ms.
New York
NY
null
null

03
Ms.
New York
NY
null
Greeting

03
Ms.
New York
NY
null
null

04
Ms.
Chicago
IL
ACT
Metro

04
Ms.
Chicago
IL
ACT
Greeting

04
Ms.
Chicago
IL
ACT
Mailing

05
null
Chicago
IL
ACT
Metro

05
null
Chicago
IL
ACT
null

05
null
Chicago
IL
ACT
Mailing


select id, salutation, city, state, acnt,
  listagg (tag, ', ') within group (order by null) as tags
from your_table
cross apply (
  select case when city = 'Chicago' then 'Metro' end as tag from dual
  union all
  select case when salutation in ('Ms.', 'Mrs.') then 'Greeting' end as tag from dual
  union all
  select case when acnt = 'ACT' then 'Mailing' end as tag from dual
)
group by id, salutation, city, state, acnt


ID
SALUTATION
CITY
STATE
ACNT
TAGS


03
Ms.
New York
NY
null
Greeting

01
Ms.
Delray Beach
FL
null
Greeting

02
Mrs.
Lauderhill
FL
DCT
Greeting

04
Ms.
Chicago
IL
ACT
Metro, Greeting, Mailing

05
null
Chicago
IL
ACT
Metro, Mailing

ID	SALUTATION	CITY	STATE	ACNT	TAG
01	Ms.	Delray Beach	FL	null	null
01	Ms.	Delray Beach	FL	null	Greeting
01	Ms.	Delray Beach	FL	null	null
02	Mrs.	Lauderhill	FL	DCT	null
02	Mrs.	Lauderhill	FL	DCT	Greeting
02	Mrs.	Lauderhill	FL	DCT	null
03	Ms.	New York	NY	null	null
03	Ms.	New York	NY	null	Greeting
03	Ms.	New York	NY	null	null
04	Ms.	Chicago	IL	ACT	Metro
04	Ms.	Chicago	IL	ACT	Greeting
04	Ms.	Chicago	IL	ACT	Mailing
05	null	Chicago	IL	ACT	Metro
05	null	Chicago	IL	ACT	null
05	null	Chicago	IL	ACT	Mailing