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.

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:

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

CodePudding user response：

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:

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

SELECT *, element_at(name_map, name) as name

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