SQL - Presto : How to select multiple columns based on LIKE operator?

Question

I have a table like this

and in SQL query I want to select only columns which are matching in where condition, e.g. if I say columns IN (x,y) then it should return x_1, x_2, x_3, y_1, y_2, y_3 columns, like this

What I tried

First I tried to use cases but the execution of query stopped once first condition has met

SELECT id,
CASE
    WHEN 'x' IN ({{ type }}) THEN 
        x_1,x_2,x_3
    WHEN 'y' IN ({{ type }}) THEN 
        y_1,y_2,y_3
    ELSE 'n/a'
END as type
FROM table_name

I have also tried some stackoverflow answers but no luck

sql select with column name like

how to use LIKE with column name

Use * Like operator to search all columns of table in sql query

SQL query to select columns with exact like?

Like statement across multiple columns in SQL

Multiple strings in LIKE condition - Presto SQL

Answer 1

In short - this is not possible (with pure SQL at least) in Presto/Trino.

SQL in general is not designed to return dynamic column sets. And while some db/query engines may allow some scripting to dynamically generate and execute query (or another option - dynamic pivoting) - Presto/Trino does not. The only option is to use some kind of outside scripting (which seems to already be the case) - i.e. query the columns:

select *
from  information_schema.columns
where table_name = 'table_name'
   -- AND column_name ... -- maybe filter here

And then process the results and then generate the query and execute it.

Or if you already know the columns - just dynamically create the query string.

P.S.

1. The provided query is not a valid one in Presto/Trino (even if you substitute the {{...}})
2. but the execution of query stopped once first condition has met - yes, that is exactly how the CASE is designed to work - it executes the first true condition and then returns it's matched result. Basically it is SQL way to say if-else
3. Another option you might want to consider - pivoting and/or using maps:

-- sample data
WITH dataset(x_1,x_2,y_1,y_2, z_1) as (
    values (1,2,3,4,5),
           (1,2,3,4,5),
           (1,2,3,4,5)
)
-- query
select map_filter(
                map(array['x_1', 'x_2', 'y_1', 'y_2', 'z_1'], array[x_1, x_2, y_1, y_2, z_1]),
                (k,v) -> any_match(array['x', 'y'], c -> k like c || '%') -- change array['x', 'y'] to contain needed prefixes
           )
from dataset;

Output:

            _col0
------------------------------
 {x_2=2, y_1=3, x_1=1, y_2=4}
 {x_2=2, y_1=3, x_1=1, y_2=4}
 {x_2=2, y_1=3, x_1=1, y_2=4}

Answer 2

Something like this to return x and y, but it is not dynamic:

WITH cte AS (
   SELECT 'x' as type ,id, x_1 as a,x_2 as b,x_3 as c FROM table_name
   UNION ALL
   SELECT 'y' as type ,id, y_1,y_2,y_3 FROM table_name
   UNION ALL
   SELECT 'z' as type ,id, z_1,z_2,z_3 FROM table_name
)
SELECT f1.a,f1.b,f1.c, f2.a,f2.b,f2.c
FROM cte f1
INNER JOIN cte f2 ON f1.id=f2.id and f2.type='y'
WHERE f1.type = 'x'