Skip to content
Advertisement

How to concat columns into a string in Snowflake?

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.

Output:

Any help or ideas on how I can accomplish this?

Advertisement

Answer

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

User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement