示例1
效果
--获取班级信息
SELECT * FROM ClassDemo;
--获取学生信息
SELECT * FROM StudentDemo;
--将学生信息合并到班级信息中
SELECT c.ClassID,c.ClassName,Students=STUFF((
SELECT ','+CAST(StudentID AS VARCHAR(36)) FROM StudentDemo s WHERE s.ClassID=c.ClassID FOR XML PATH('')
),1,1,'') FROM ClassDemo c
示例2
效果
SELECT * FROM City;
SELECT DISTINCT UpperID,CityID=STUFF((
SELECT ','+CAST(CityID AS VARCHAR(36)) FROM City c2 WHERE c1.UpperID=c2.UpperID FOR XML PATH('')
),1,1,'') FROM City c1;
如果sql server版本在2017以上,可以使用STRING_AGG
select p.ProjectName,STRING_AGG(b.BlockName,',') from TB_Block b
inner join TB_Project p on b.ProjectID=p.ProjectID
where b.IsInvalid=0
and p.ProjectName in ('TPQ项目(SXTD)','WH项目(SXTD)')
group by p.ProjectName