Snowflake Dynamic SQL to select subset of columns in one table from undetermined number of values in another table

Question

I need to create a SQL script that will run daily and select a subset of columns from table A. The subset of columns is defined in table B - it will be periodically edited by marketing team and can have any subset of the columns in table A

example:

Table_A has 100 columns: [Column1], [Column2], ..., [Column100]

Table_B has 1 column: [ColumnNames] with the values 'Column1', 'Column3', 'Column8'

How do I write a dynamic query

SELECT **Column1, Column3, Column8**
FROM Table_A

where the list of columns (in bold) comes from the query

SELECT LISTAGG(DISTINCT ColumnNames, ', ')
FROM Table_B 

I tried the following:

SET ColumnList = (SELECT LISTAGG(DISTINCT ColumnNames, ', ') FROM Table_B );
SET SQLScript = (SELECT CONCAT('SELECT ', $ColumnList, ' FROM Table_A'));
EXECUTE IMMEDIATE $SQLScript;

This works well as long as the character size of the variable SQLScript does not exceed 256, but I cannot count on that being true since marketing may select to include many ColumnNames in Table_B

Answer 1

Using the style as suggested by @Mat on their comment, with 500 columns:

EXECUTE IMMEDIATE $$
    BEGIN   
        let concat_cols string := (
            select listagg(x, ', ') 
            from (select a.$1 x from values('a'),('b'),('c') a 
            join (select 1 from TABLE(GENERATOR(ROWCOUNT => 500))))
            );
        let long_select string := 'select ' || concat_cols || ' from (select 1 a, 2 b, 3 c)';
        let res resultset := (EXECUTE IMMEDIATE long_select);
        return table(res);
    END;
$$
;

These variables don't have the 256 character limit.

https://docs.snowflake.com/en/developer-guide/snowflake-scripting/variables