CODEWITHSUNDEEP
sqloracle-databasestuff
Toby
Toby

Reputation: 145

SQL tagging - Returning new column in results

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.

Upvotes: 1

Views: 100

Answers (3)

Matthew McPeak
Matthew McPeak

Reputation: 17944

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.

Upvotes: 3

Alex Poole
Alex Poole

Reputation: 191570

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

Upvotes: 3

Rajat
Rajat

Reputation: 5803

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

Upvotes: 0

Related Questions