Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
03-30-2023 05:59 AM
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