oleole
Contributor

Posting the solution that I ended up using:

%sql
 DROP TABLE if exists UserCountry;
  CREATE TABLE if not exists UserCountry (
   UserID INT,
   Country VARCHAR(5000)
  );
  INSERT INTO UserCountry
  SELECT 
   L.UserID AS UserID,
   CONCAT_WS(',', collect_list(LC.Country)) AS Country
   COALESCE(
   STUFF(
   (SELECT ', ' + LC.Country FROM UserStopCountry LC WHERE L.UserID = LC.UserID FOR XML PATH (''))
   , 1, 2, '')
   , '') AS Country
  FROM LK_ETLRunUserID L
  INNER JOIN UserStopCountry LC
  ON L.UserID = LC.UserID
  GROUP By L.UserID

View solution in original post