Q: Listagg function issue in Python script I am using a listagg function that is concatenating the attribute names present in a table. This is then used in a pivot function to create a view on top of the pivoted data. While the listagg function by itself is returning a proper output but when the view is being checked, the names of the individual columns (which is separated with comma) along with the commas are being taken as one big string. Can you please help me with this?
Output of listagg:
("'Region','Sub-Region','Division','Company-Product','Company-Mfg','Company-Ship From to Customer','Business Unit','Category','Sub-Category','Segment','Sub-Segment','Brand','Aggregate Brand','Sub-Brand','PP'",)
Output of view:

Code:
import snowflake.connector as sf
import pandas

ctx = sf.connect (
    user = 'floatinginthecloud89',
    password = '',
    account = 'nq13914.southeast-asia.azure',
    warehouse = 'compute_wh',    
    database = 'util_db',
    schema = 'public'
)
print("Got the context object")
 
cs = ctx.cursor()
print("Got the cursor object")

column1 = 'attr_name';


try:
     row = cs.execute("select listagg(('''' || attr_name || ''''), ',') from util_db.public.TBL_DIM;")
     rows = cs.fetchall()
     for row in rows:
          print(row)
          print(rows)
          
     row1 = cs.execute("""CREATE OR REPLACE table util_db.public.HIERARCHY_VIEW_2 AS 
        SELECT * FROM (
            SELECT MSTR.PROD_CODE AS PROD_CODE,DIM.ATTR_NAME AS ATTR_NAME,MSTR.ATTR_VALUE AS ATTR_VALUE 
            FROM TBL_DIM DIM 
            INNER JOIN TBL_MSTR MSTR 
                ON DIM.ATTR_KEY=MSTR.ATTR_KEY 
        ) Q
        PIVOT (MAX (Q.ATTR_VALUE) FOR Q.ATTR_NAME IN (%s))
        AS P
        ORDER BY P.PROD_CODE;""", (rows))
     rows1 = cs.fetchall()
     for row1 in rows1:
          print(row1)     
          

finally:
     cs.close()     
ctx.close() 


A: The error you are getting is because you are parsing as string (which happens to look like a parameter list) to as a parameter, on the execute. This is the safe way to take and use parameter to avoid SQL injection, and thus your string is being handled as "one string". But given you are the source of the string you can use "generally unsafe methods" and do the string concatenation in python and execute that.
So separating the string formatting into one step and then executing the result would by like:
_sql = """CREATE OR REPLACE table util_db.public.HIERARCHY_VIEW_2 AS 
    SELECT * FROM (
        SELECT MSTR.PROD_CODE AS PROD_CODE,DIM.ATTR_NAME AS ATTR_NAME,
            MSTR.ATTR_VALUE AS ATTR_VALUE 
        FROM TBL_DIM DIM 
        INNER JOIN TBL_MSTR MSTR 
            ON DIM.ATTR_KEY=MSTR.ATTR_KEY 
    ) Q
    PIVOT (MAX (Q.ATTR_VALUE) FOR Q.ATTR_NAME IN (%s))
    AS P
    ORDER BY P.PROD_CODE;""".format(rows)

row1 = cs.execute(_sql)

As you mention in the comment, the other option of not string concatenating in SQL and then split, and concatenating in Python is not working for you.

A: I did not use the listagg function but went ahead with using a for loop to generate my aggregation.
