How to call a variable inside dynamic SQL Query in Snowflake stored procedure

i tried this and was able to execute, let me know if this is what you wanted.

    create table PARSEDNUM(name char(2000)) ;
create table PARSEDCHAR(name char(2000));
insert into PARSEDNUM values('PARSEDNUM');
insert into PARSEDNUM values('PARSEDCHAR');

CREATE OR REPLACE PROCEDURE SP_TES()
RETURNS VARCHAR NOT NULL
LANGUAGE SQL
EXECUTE AS CALLER
AS 
$$
DECLARE 
    
    allcols VARCHAR(16777216);
 
    sql_text_test VARCHAR(16777216);
    
BEGIN

           SELECT LISTAGG( '''' || NAME || '''', ',') WITHIN GROUP (ORDER BY NAME) 
            INTO :allcols
            FROM(SELECT DISTINCT NAME FROM PARSEDNUM UNION SELECT DISTINCT NAME FROM PARSEDCHAR) 
           AS NAMES;

         sql_text_test := ' 
                CREATE OR REPLACE TEMP TABLE TEST as 
                    select
                        a.DATA_KEY_ as DATA_KEY_, 
                         ' || :allcols || '
                        
                    from transposednum AS a 
                    left join transposedchar AS b
                    on a.DATA_KEY_ = b.DATA_KEY_
                
            ';
      --      EXECUTE IMMEDIATE sql_text_test;```
          
    RETURN sql_text_test;

END;
$$;

 call SP_TES();

The above execution returns the following statement, CREATE OR REPLACE TEMP TABLE TEST as select a.DATA_KEY_ as DATA_KEY_, 'PARSEDCHAR','PARSEDNUM'

                    from transposednum AS a 
                    left join transposedchar AS b
                    on a.DATA_KEY_ = b.DATA_KEY;

I created a table as follows, create table transposednum (DATA_KEY_ char(200)); create or replace table transposedchar (DATA_KEY char(200));

It returns error on the "'PARSEDCHAR','PARSEDNUM'" , are they the new columns that you will create or they are existing in the table transposednum or transposedchar ?

If you want to create table on the basis of columns in the PARSEDCHAR or PARSEDNUM table then you can use the column_name from information_schema.columns views.