SQL tagging - Returning new column in results

Question

I need to create a new column for my query labeled Tags

There are three tags and the definitions are below:

Metro: City = Chicago

Mailing: ACNT = 'ACT'

Greeting: Salutation in ('Ms.','Mrs.')

Current table:

 ID    Salutation    City          State    ACNT 
 01    Ms.           Delray Beach  FL                   
 02    Mrs.          Lauderhill    FL       DCT
 03    Ms.           New York      NY    
 04    Ms.           Chicago       IL       ACT
 05                  Chicago       IL       ACT

I need to add a column Tags to my output, like this.

 ID    Salutation    City          State    ACNT   Tags
 01    Ms.           Delray Beach  FL              Greeting     
 02    Mrs.          Lauderhill    FL       DCT    Greeting
 03    Ms.           New York      NY              Greeting
 04    Ms.           Chicago       IL       ACT    Metro, Greeting, Mailing
 05                  Chicago       IL       ACT    Metro, Mailing

I have used Stuff before but not in this manner. Any help/guidance would be appreciated.

Answer 1

If you're on a recent version of Oracle you can use cross-apply with a derived table of tags (using case expressions, as @serfe suggested):

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
)

and then use listagg() to get the list in the form you want:

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

db<>fiddle