select multi record to one row Sqlserver

Question

Select distinct makien, madh_rep,  tenmh, sku, thoigian, nguoilap,
    substring(
        (
            Select  distinct '|'+ST1.product_code  AS [text()]
            From dbo.tbl_thanhpham_rep_chitiet ST1
            Where ST1.makien = ST2.makien         
            For XML PATH ('')
        ), 2, 1000) product_code
From dbo.tbl_thanhpham_rep_chitiet ST2

Answer 1

Khả dụng trên SQL Server 2017 or higher

select KhoaID, STRING_AGG(MonHocID, ', ') AS MH from tblKhoaMonhoc GROUP BY KhoaID

Result:
1      2, 3, 5
2      1, 2
3      4
4      1, 9, 8, 5, 6