Dynamic column fields using existing column values in SQL

Question

I have this existing query

Select 
   mt.First_name,
   mt.Last_name as OLD_Last_name,
   ot.Last_name as New_Last_name,
   ot.Date as Update_Date,
from maintable as mt
JOIN othertable as ot on mt.id=ot.id

I'd like to join a new column with the following output: [mt.First_name] [ot.Last_name], nee [mt.Last_name] changed their name on [ot.Date]. I tried using a case statement but didn't get it right.

SELECT mt.First_name || ' ' || ot.Last_name || ', nee ' || mt.last_name || ' changed their name on ' || ot.Date AS yournewcolumn, mt.First_name, mt.Last_name as OLD_Last_name, ot.Last_name as New_Last_name, ot.Date as Update_Date from maintable as mt JOIN othertable as ot on mt.id=ot.id — JNevill
– JNevill, Commented Aug 8, 2022 at 21:00

Felipe Hoffa · Accepted Answer · 2022-08-09 22:14:01Z

1

For closure, moving @Jnevill answer from comment to actual answer:

SELECT mt.First_name || ' ' || ot.Last_name || ', nee ' || mt.last_name || ' changed their name on ' || ot.Date AS yournewcolumn, mt.First_name
  ,    mt.Last_name as OLD_Last_name
  ,    ot.Last_name as New_Last_name
  ,    ot.Date as Update_Date 
from maintable as mt 
JOIN othertable as ot on mt.id=ot.id

Apparently OP wanted to know how to concatenate strings, which is done with ||.

answered Aug 9, 2022 at 22:14

Felipe Hoffa

59.8k23 gold badges185 silver badges365 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Dynamic column fields using existing column values in SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related