CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
Payal
Payal

Reputation: 11

Need distinct values from two tables and I need to CONCAT them to get final unique rows

I have tried to create a scenario. I'm trying to achieve this in snowflake. ID is the primary key in Table 2 and two tables can be joined using ID column.

For example - If the output of the query comes out as - 2, Er1, Er2, Er2, Er4 Then, First Er2 is coming from Table1 and Second Er2 is coming from Table2. In this case second Er2 should not be considered and the final output should be - 2, Er1, Er2, Er4

I have tried in below ways but getting few duplicates.

SELECT E.ID,
     CASE WHEN listagg ( E.ERROR_NAME || ',' || I.ERROR_NAME ) LIKE '%,'
     THEN LEFT (listagg ( E.ERROR_NAME || ',' || I.ERROR_NAME ), LEN(listagg (E.ERROR_NAME || ',' || I.ERROR_NAME))-1)
      ELSE listagg (E.ERROR_NAME || ',' || I.ERROR_NAME)
      END AS ERROR_NAME_CUSTOM
  from TABLE1  E
  JOIN TABLE2 I ON I.ID = E.ID
    group by E.ID

Tables

Upvotes: 0

Views: 70

Answers (1)

Greg Pavlik
Greg Pavlik

Reputation: 11046

There's probably a more elegant solution here, but this works:

with
ERR as
(
select           T1.ID
                ,T1.ERROR_NAME || ',' || T2.ERROR_NAME as ERROR_NAMES
from            TABLE1 T1
    left join   TABLE2 T2
        on      T1.ID = T2.ID
),
COMBINED as
(
select * from ERR, lateral split_to_table(ERROR_NAMES, ',')
)
select ID, listagg(distinct "VALUE", ',') within group (order by "VALUE") from COMBINED group by ID
order by ID
;

Upvotes: 1

Related Questions