admin管理员组

文章数量:1290234

I am trying to use LISTAGG to create one column as a list that is the product of two columns. I am able to execute the query, but when using DISTINCT it still does not remove the duplicate values. Code sample below.

CASE WHEN PLA.SALES_CTRY_LIST IS NULL AND PLA.DP_CTRY_LIST IS NOT NULL 
             THEN PLA.DP_CTRY_LIST 
             WHEN PLA.DP_CTRY_LIST IS NULL AND PLA.SALES_CTRY_LIST IS NOT NULL 
             THEN PLA.SALES_CTRY_LIST 
             WHEN PLA.SALES_CTRY_LIST IS NOT NULL AND PLA.DP_CTRY_LIST IS NOT NULL 
             THEN LISTAGG(DISTINCT PLA.SALES_CTRY_LIST ||', '||PLA.DP_CTRY_LIST)
        END AS "TESTING"

I am trying to use LISTAGG to create one column as a list that is the product of two columns. I am able to execute the query, but when using DISTINCT it still does not remove the duplicate values. Code sample below.

CASE WHEN PLA.SALES_CTRY_LIST IS NULL AND PLA.DP_CTRY_LIST IS NOT NULL 
             THEN PLA.DP_CTRY_LIST 
             WHEN PLA.DP_CTRY_LIST IS NULL AND PLA.SALES_CTRY_LIST IS NOT NULL 
             THEN PLA.SALES_CTRY_LIST 
             WHEN PLA.SALES_CTRY_LIST IS NOT NULL AND PLA.DP_CTRY_LIST IS NOT NULL 
             THEN LISTAGG(DISTINCT PLA.SALES_CTRY_LIST ||', '||PLA.DP_CTRY_LIST)
        END AS "TESTING"

  • snowflake-cloud-data-platform
  • listagg
Share Improve this question asked Feb 20 at 3:41 user3496218user3496218 2093 gold badges8 silver badges19 bronze badges 2
  • 1 It should be removing duplicates; check that your "duplicate" data is really the same. – Tim Biegeleisen Commented Feb 20 at 4:10
  • Thanks. I checked that both are text types and have the same length (checking for spaces etc.) – user3496218 Commented Feb 20 at 4:17
Add a comment  | 

1 Answer 1

Reset to default 1

The problem here is that you're using a concatenation statement, so that distinct is looking at unique concatenations, not the unique elements within the list.

Maybe consider something like this:

CASE
       WHEN PLA.SALES_CTRY_LIST IS NULL AND PLA.DP_CTRY_LIST IS NOT NULL
           THEN PLA.DP_CTRY_LIST
       WHEN PLA.DP_CTRY_LIST IS NULL AND PLA.SALES_CTRY_LIST IS NOT NULL
           THEN PLA.SALES_CTRY_LIST
       WHEN PLA.SALES_CTRY_LIST = PLA.DP_CTRY_LIST
           THEN PLA.SALES_CTRY_LIST
       WHEN PLA.SALES_CTRY_LIST IS NOT NULL AND PLA.DP_CTRY_LIST IS NOT NULL AND PLA.SALES_CTRY_LIST != PLA.DP_CTRY_LIST
           THEN PLA.SALES_CTRY_LIST || ', ' || PLA.DP_CTRY_LIST
       END AS "TESTING"

This way, if the two values are the same, then you only list the one instance, and if the two values are different, you make a list of them.

You could also simplify some of those earlier clauses using COALESCE.

本文标签： snowflake cloud data platformHow to use LISTAGG on multiple columns and remove duplicatesStack Overflow

版权声明：本文标题：snowflake cloud data platform - How to use LISTAGG on multiple columns and remove duplicates - Stack Overflow 内容由网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://www.betaflare.com/web/1741459791a2379967.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。