更新时间:2023-01-31 16:18:46
借助 FOR XML PATH
和 STUFF 来连接值,您可以使用您想要的结果轻松更新表 District
.
UPDATE a
SET a.Schools = b.SchoolList
FROM Districts a
INNER JOIN
(
SELECT DistrictId,
STUFF((SELECT ', ' + SchoolName
FROM Schools
WHERE DistrictId = a.DistrictId
FOR XML PATH (''))
, 1, 1, '') AS SchoolList
FROM Districts AS a
GROUP BY DistrictId
) b ON A.DistrictId = b.DistrictId
WHERE b.SchoolList IS NOT NULL