select  Name + ','  from  A  FOR XML PATH('')

若 Name不是 varchar类型，先转

select  CAST(Name AS varchar) + ','  from  A  FOR XML PATH('')

去，号

select left(a,len(a)-1) from  (select  (select  CAST(Name AS varchar) + ','  from A FOR XML PATH('')) a)b

 接下来我们利用STUFF结合FOR XML PATH来拼接JSON字符串，如下

DECLARE @content VARCHAR(MAX)

SET @content = (SELECT '['+ STUFF((SELECT TOP 5 ',{"ID": "' +  CONVERT(VARCHAR, ID) + '","Years": "' + CONVERT(VARCHAR, Years) + '","Name": "' + CONVERT(VARCHAR, Name) + '","ZipCode": "' + CONVERT(VARCHAR, ZipCode, 105) + '"}' FROM CMS_InfoPublish_N_ServiceObject_Type_People 
 FOR XML PATH('')), 1, 1,''
 ) 
 + ']')

PRINT @content 

加个类别列

select 
	类别, 
	名称 = (
		stuff(
			(select ',' + 名称 from Table_A where 类别 = A.类别 for xml path('')),
			1,
			1,
			''
		)
	) 
from Table_A as A group by 类别

补充：

更多stuff用法参考https://www.cnblogs.com/CreateMyself/p/9058380.html

更多FOR XML PATH用法参考https://www.cnblogs.com/yasuo2/p/6433697.html