SELECTparent,STUFF( (SELECT','+child FROMt a WHEREb.parent=a.parent FORXML PATH('')),1,1,'') children FROMt b GROUPBYparent 如果你还有其他的解决办法,希望你也能给出你的答案, 多多益善 其他的方案 2014-5-5 update: 另外,有同学问,怎么逆向还原回去呢?很简单: 1 2 3 4 5 6 7 8 9 1...
createtablepd(col1varchar(5),col2int)insertintopdselect'A',3unionallselect'A',2unionallselect'A',1unionallselect'B',2unionallselect'B',1selecta.col1,stuff((select','+rtrim(b.col2)frompd bwhereb.col1=a.col1orderbyb.col2forxml path('')),1,1,'')'col2'frompd agroupbya.col1...
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...
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 ...
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('')) ...
New Stuff: Resources for Your Developer Toolbox Web Q&A: InfoPath Back End, WSH Script Signing, and More Using the .NET Compact Framework, SQL Server CE, and Replication Cutting Edge: Managing Your Remote Windows Clipboard Introducing the Web Services Enhancements 2.0 Messaging API ...
When we first looked at the XML elements for arguments, we got kind of excited. (Well, as excited you can get by looking at XML elements.) That’s because you can mark an argument as being required. Combined with the usage stuff, we thought this was going to be really cool; we ...
ORDER BY ID FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)') SELECT T.c.value('(Property[@Name="DriveLetter"])[1]','CHAR(1)') as DriveLetter, T.c.value('(Property[@Name="Size"])[1]','BIGINT')/POWER(1024.0,3) as SizeGB, T.c.value('(Property[@Name="SizeRemaining"...
SELECT STUFF((SELECT ' ' + UPPER(SubString(Item,1,1)) + LOWER(SubString(Item,2,len(Item))) FROM dbo.DelimitedSplit8K(@data, ' ') p ORDER BY ItemNumber FOR XML PATH ('')),1,1,'') Edit: added proper casing of result WayneMicrosoft Certified Master: SQL Server 2008Author - SQL ...
I’ve said enough about this book in the first three words of this post. I was putting it off for a while, and now there are no excuses. SQL Server 2012 Practices I don’t do DBA stuff at work, still I enjoy learning about things that impact me; like release management, auditing,...