SQL SSMS merge IDs into row grouping by dates

Question

I have two tables:

Main table

ID	Date	Device
252708	2022-01-01	Phone
252708	2022-01-01	Email
252252	2022-01-02	Phone
252252	2022-01-02	Phone
252252	2022-01-02	Phone
253022	2022-01-06	Phone
253022	2022-01-06	Phone
253228	2022-01-06	Email
253228	2022-01-06	Email
252708	2022-01-06	Phone
256703	2022-01-09	Phone

Date table

Date	Week
2022-01-01	WK 17
2022-01-02	WK 18
2022-01-03	WK 18
2022-01-04	WK 18
2022-01-05	WK 18
2022-01-06	WK 18
2022-01-07	WK 18
2022-01-08	WK 18
2022-01-09	WK 19
2022-01-10	WK 19
2022-01-11	WK 19

I want to merge the IDs into rows, grouping by Wk (using my date table)

ID	Date	Device_1	Wk
252708	2022-01-01	Phone, Email	WK17
252252	2022-01-02	Phone, Phone, Phone	WK18
253022	2022-01-06	Phone, Phone	WK18
253228	2022-01-06	Email, Email	WK18
252708	2022-01-06	Phone	WK18
256703	2022-01-09	Phone	WK19

I know I need the string_agg function to merge the devices into rows, however, I'm not sure how to separate by week. Thanks in advance

Answer 1

You can always use "FOR XML" instead of STRING_AGG. It would be something like this:

select 
    distinct(dev.ID)
    ,dev.Date
    ,(
        select  
            Device + ',' as [text()]
        FROM MainTable a
        JOIN DateTable b on a.Date = b.Date
        Where dev.ID = a.ID and b.Week = cal.Week
        FOR XML PATH ('')
    ) as Device_1
    ,cal.Week
FROM MainTable dev
JOIN DateTable cal on dev.Date = cal.Date
Order By Week