Add a flag based on occurrence of values in snowflake

Question

I want to add a flag to a table based on the occurrence of a specific value based on other columns.

City    Product Manufacturer    Flag
1          2        1           man_1
1          3        1           man_1_2
1          4        2           man_2
1          3        2           man_1_2
2          6        1           man_1
2          6        1           man_1
2          6        1           man_1
2          6        1           man_1
3          3        1           man_1_2
3          3        2           man_1_2

I want to add a flag based on the product provided by the manufacturer in a city. If a product is available from both the manufacturers in the city a flag of 'man_1_2' is needed. Similarly, if a product is unique to the manufacturer in the city flag of 'man_1' or 'man_2' is needed. The data only consists of two manufacturers.

Thanks for the help!

Answer 1

You can use the listagg window function. It will work in a more general sense. If you wind up with more than two manufacturers per city, it will still work.

select   CITY
        ,PRODUCT
        ,MANUFACTURER
        ,'MAN_' || listagg(distinct MANUFACTURER, '_')
            within group (order by MANUFACTURER)
            over (partition by CITY, PRODUCT) as FLAG
from T1
;

CITY	PRODUCT	MANUFACTURER	FLAG
1	2	1	MAN_1
1	3	1	MAN_1_2
1	4	2	MAN_2
1	3	2	MAN_1_2
2	6	1	MAN_1
2	6	1	MAN_1
2	6	1	MAN_1
2	6	1	MAN_1
3	3	1	MAN_1_2
3	3	2	MAN_1_2