In Snowflake, Find every city mapped to an employee (from any row) and display them concatenated for every row where this employee is present

Lara L 0
Viewed 31

Using Snowflake, In the example below, I am trying to concatenate all the city values found on any row for each employee and display them all (Concatenated) for each row where the user appears.

Example I have the table below:

employee city color
john montreal blue
john new york yellow
john yukon red
mark san francisco orange
mark baltimore purple
ivan london black

But my Expected result is the one below:

employee city color
john montreal ,new york ,yukon blue
john montreal ,new york ,yukon yellow
john montreal ,new york ,yukon red
mark san francisco,baltimore orange
mark san francisco,baltimore purple
ivan london black

Is there a snowflake query that could help me achieve this?

Thank you

concatenation snowflake-cloud-data-platform sql
1 Answers

We can try using LISTAGG() as an analytic function:

SELECT employee,
       LISTAGG(city, ',') WITHIN GROUP (ORDER BY city) OVER (PARTITION BY employee) city,
       color
FROM yourTable
ORDER BY employee;
Tim Biegeleisen0
Related