SQL Server String Aggregation stitching method

Examples of data are:

To get the result of the target, get the same type of all names stitching together the string:

SQL Server does not have a direct concatenation of the function of the string, the method mentioned below, just the daily development of their own personal use of some ideas, for reference only!

Declare @tempTable Table ([Type]int,[Name]nvarchar(100 ))

Create table variables, fields are values for each column you need to return

Insert @tempTable Select [type],MAX([name]) namefrom  TestGroupby[type]

Inserting the initial aggregated data

Updatetag:Update @tempTableSet [name] +=(','+A.[name]) fromTest A,@tempTablebwhereA.[Type] =B.[Type]  and CHARINDEX(A.[name]B.[name])= 0if @ @ROWCOUNT > 0begin    GOTOUpdatetagEndSelect *  from @tempTable

Looping through other aggregated data that satisfies the conditions, such as the need to query aggregated multi-column concatenation string results, and the complex operations of some columns, but the disadvantage is also obvious, if the group aggregation of the items are more time-consuming, Because there is a loop efficiency is not very high insert, but for dozens of or hundreds of times of the cycle is still not much of a problem.

In fact, the search on the internet is a good way, such as the stuff function, we can write the above results:

SELECT [TYPE],STUFF(( SELECT ','+ [Name]  fromTest BWHEREB.type=A.type forXML PATH ("')),1,1,"')[Names] fromTest aGroup  by [TYPE]

As for this function usage, there is not much to explain, I hope the above two options for you a little help or revelation!

SQL Server String Aggregation stitching method