在网上的其他方法中也有把逗号加在value前,然后用stuff函数来去掉最前面多余的逗号的,详见参考链接。substring的缺点是用len函数的时候还要再填一遍“select value+',' from tb where ID=1 for xml path('')“,会显得冗长。 最后完整的sql如下: selectid, valuelist=substring((selectvalue+','fromtbwheretb....
FOR XML PATH('') [,ROOT('')]:按PATH定义具体的XML结构。 FOR XML语句可以配合字段别名一起定义生成的XML结构。具体可以参考下面的示例。 返回XML 需要特别声明一下,FOR XML PATH语句可以用来方便的实现列合并。 比如要对上面的Book表合并Name字段,以“;”分隔每项: 1SELECTSTUFF((SELECT';'+NameFROM[dbo]...
这里我们通过XML类型和REPLACE()函数实现字符串的分裂 --需要 DECLARE @strVARCHAR(MAX)='1,2,3,4,'; DECLARE @xmlstr XML; SETARITHABORTON; SET@xmlstr=CONVERT(XML,'<root><v>'+REPLACE(@str,',','</v><v>')+'</v></root>'); --SELECT @xmlstr DECLARE @tableVarTABLE(F1VARCHAR(100)) ...
ASTEMP--拼sqlDECLARE@valuekeyASNVARCHAR(MAX)DECLARE@queryASNVARCHAR(MAX)DECLARE@mtrASNVARCHAR(MAX)SELECT@valuekey=STUFF((SELECTN',SUM(ISNULL(['+[Valuekey]+'], 0)) AS '+QUOTENAME([Valuekey])FROM#TemapetGROUPBY[Valuekey]ORDERBY[Valuekey]FORXMLPATH(''),TYPE).value('.','NVARCHAR(MAX)')...
Alternative way STUFF for XML PATH ('') ? Am getting an error when i run this code Ambiguous Column Name An aggregate may not appear in the set list of an UPDATE statement... An error occurred while executing batch. Error message is: Error creating window handle. SQL SERVER 2008 An err...
2016-06-18 14:05 −示例 stuff: for xml path 参考文章 示例 昨天遇到一个SQL Server的问题:需要写一个储存过程来处理几个表中的数据,最后问题出在我想将一个表的一个列的多行内容拼接成一行,比如表中有两列数据 : 类别名称 AAA 企业1 AAA 企业2 AAA 企业3... ...
for xml path('') ), 1, 1, '') AS USERS FROM GROUPS G ORDER BY G.GROUP_NAME ASC; AMB Thursday, September 17, 2009 7:57 PM |2 votes You can do it like this: SELECT Teacher ,StudentList=STUFF((SELECT ','+First_Name FROM Students WHERE Teacher=A.Teacher FOR XML PATH('')) ...
Powerful T-SQL Syntax Gives SQL Server a Programmability Boost New Stuff: Resources for Your Developer Toolbox The XML Files: XML Report from the Microsoft PDC 2003 Wicked Code: Client-side Paging for DataGrids XML in SQL Server: Native XML Type and Advanced Data Handling Editor's Note: Yuko...
SELECT STUFF(( SELECT ',' + FirstName + LastName FROM AdventureWorks2016CTP3.Person.Person FOR XML PATH('') ,TYPE ).value('.', 'nvarchar(max)'), 1, 1, '') AS RawResult On first glance, it may seem counter-intuitive that the second version could be faster, but that is indeed ...
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有...