How to concat columns into a string in Snowflake?

April 19, 2022

I have this table Movie with columns defined as (CustomerId, MovieId, Name) and I want to fetch these columns by concatenating them into a string in the position they are defined in Snowflake, by ordering through ORDINAL_POSITION.

So I found that using LISTAGG() get’s me pretty close to what I want. By doing the following I get close to my desired string, but I get duplicates of the column names. I’m seeing that people will use group by and order by based on a column in the table instead of using ORDINAL_POSITION. So this is what I’m struggling to find a solution. I’ve found that COALESCE could help since it returns the first Non-Null value, however, I’m struggling to integrate this into my Query.

//Query
select listagg(column_name, ',') within group (order by ORDINAL_POSITION) 
from information_schema.columns 
where table_name='Movie';

Output:

CUSTOMERID,CUSTOMERID,CUSTOMERID,MovieId,MovieId,MovieId,
Name,Name,Name

Any help or ideas on how I can accomplish this?

>Solution :

The column names should be unqiue per table by design. Probably table exists in multiple schemas/databases:

select listagg(column_name, ',') within group (order by ORDINAL_POSITION) 
from information_schema.columns 
where table_name='Movie'
  and table_schema = 'XXX'
  and table_catalog = 'YYY';