SQL tagging - Returning new column in results-CodePudding

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                  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.

CodePudding user response：

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

CodePudding user response：

An expression for TAGS would be:

 RTRIM(
   CASE WHEN salutation IN ('Ms.','Mrs.') THEN 'Greeting' || ', ' ELSE '' END ||
   CASE WHEN city = 'Chicago' THEN 'Metro' || ', ' END ||
   CASE WHEN acnt = 'ACT' THEN 'Mailing' END
 ,', ')

You could use that expression in an UPDATE statement to set the value for a new TAGS column. Or, you could put it in a view or SQL query or virtual column to compute the TAGS value as needed.

CodePudding user response：

You can do it with CASE - WHEN statement. More info here: https://www.w3schools.com/sql/sql_case.asp

CodePudding user response：

If you're open to a slight variation in how the tags looks, it can get easier with a concat using ||

select t.*, case when city='Chicago' then '(Metro)' else '' end ||
            case when salutation in ('Ms.','Mrs.') then '(Greeting)' else '' end ||
            case when acnt = 'ACT' then '(Mailing)' else '' end as tags
from your_table t;

Outputs

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)

With a slight tweak to Matthew's great solution, you can also do the following to get exactly what you want

select t.*, ltrim(case when city='Chicago' then 'Metro' else '' end ||
                  case when salutation in ('Ms.','Mrs.') then ',Greeting' else '' end ||
                  case when acnt = 'ACT' then ',Mailing' else '' end,',') as tags
from your_table t