Create a hardcoded "mapping table" in Trino SQL

Question

I have a query (several CTEs) that get data from different sources. The output has a column name, but I would like to map this nameg to a more user-friendly name.

Id	name
1	buy
2	send
3	paid

I would like to hard code somewhere in the query (in another CTE?) a mapping table. Don't want to create a separate table for it, just plain text.

name_map=[('buy', 'Item purchased'),('send', 'Parcel in transit'), ('paid', 'Charge processed')]

So output table would be:

Id	name
1	Item purchased
2	Parcel in transit
3	Charge processed

In Trino I see the function map_from_entries and element_at, but don't know if they could work in this case.

I know "case when" might work, but if possible, a mapping table would be more convenient.

Thanks

Answer 1

Super interesting idea, and I think I got it working:

with tmp as (
    SELECT * 
    FROM (VALUES ('1', 'buy'),
                  ('2', 'send'),
                  ('3', 'paid')) as t(id, name)
)
SELECT element_at(name_map, name) as name
FROM tmp
JOIN (VALUES map_from_entries(
    ARRAY[('buy', 'Item purchased'),
          ('send', 'Parcel in transit'),
          ('paid', 'Charge processed')])) as t(name_map) ON TRUE

Output:

name
Item purchased
Parcel in transit
Charge processed

To see a bit more of what's happening, we can look at:

SELECT *, element_at(name_map, name) as name

id	name	name_map	name
1	buy	{buy=Item purchased, paid=Charge processed, send=Parcel in transit}	Item purchased
2	send	{buy=Item purchased, paid=Charge processed, send=Parcel in transit}	Parcel in transit
3	paid	{buy=Item purchased, paid=Charge processed, send=Parcel in transit}	Charge processed

I'm not sure how efficient this is, but it's certainly an interesting idea.