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