PostgreSQL SELECT and REPLACE multiple strings into a single column

Question

What is the optimal way to replace multiple strings and return the results of the replace operations in a single column.

The following query works perfectly, however it generated 5 columns obviously.

SELECT 
REPLACE(l.status, 'New Onboarding Request', '1. New Lead'),
REPLACE(l.status, 'Appointment Accepted', '2. Appointment Accepted'),
REPLACE(l.status, 'Accepted', '3. Property Eligible'),
REPLACE(l.status, 'Owner Accepted', '4. Terms Accepted'),
REPLACE(l.status, 'Onboarding', '5. Onboarding'), 
l.id, o.name as partner, v.lat as latitude, v.lng as longitude, v.postcode, 
l.status, v.bedrooms, v.bathrooms, "v"."houseOrFlat", to_char("v"."created_at", 'YYYYMMDD') as valuation_date, to_char("l"."created_at", 'YYYYMMDD') as referral_date, to_char("l"."updated_at", 'YYYYMMDD') as updated,  v.estimated_nightly_rate, v.furnished   FROM valuations v
LEFT JOIN agent_onboarding_requests l on v.id = l.valuation_id
INNER JOIN organisations o on o.id = l.organisation_id
WHERE l.organisation_id != 1
ORDER BY referral_date ASC

Answer 1

You can also create a inline table with VALUES and join it to your existing table:

SELECT
    s.status old_status,
    n.new_status
FROM
  statuses s
JOIN
  (VALUES ('New Onboarding Request', '1. New Lead'),
          ('Appointment Accepted','2. Appointment Accepted'),
          ('Accepted', '3. Property Eligible'),
          ('Owner Accepted', '4. Terms Accepted'),
          ('Onboarding', '5. Onboarding')) n(old_status, new_status)
  ON s.status = n.old_status

or with a CTE (WITH):

WITH n(old_status, new_status) AS
(VALUES ('New Onboarding Request', '1. New Lead'),
        ('Appointment Accepted','2. Appointment Accepted'),
        ('Accepted', '3. Property Eligible'),
        ('Owner Accepted', '4. Terms Accepted'),
        ('Onboarding', '5. Onboarding'))
SELECT
    s.status old_status,
    n.new_status
FROM
  statuses s
JOIN n ON s.status = n.old_status

Answer 2

In the end this struck me as a more elegant way than nested REPLACE

SELECT 

    CASE 
        WHEN(l.status = 'New Onboarding Request') THEN '1. New Lead'
        WHEN(l.status = 'Appointment Accepted') THEN '2. Appointment Accepted'
        WHEN(l.status = 'Accepted') THEN '3. Property Eligible'
        WHEN(l.status = 'Owner Accepted') THEN '4. Terms Accepted'
        WHEN(l.status = 'Onboarding') THEN '5. Onboarding'
    END AS status_modified,