SQL Server stitching a column of fields into a string method

Recently encountered a problem in the project, you need to merge a column field in the table into a string output, if read directly through the code, and then traverse the stitching is obviously not the best way, so think of the data can be read when the direct stitching back, search on the Web can be achieved through the FOR XML.

First, prepare the required data with the following script:

if exists(Select *  fromSysObjectswhereId=object_id('Student'))    Drop TableStudentGoCreate TableStudent (Idint, Namevarchar( -))Go Insert  intoStudentValues(1,'Zhang San');Insert  intoStudentValues(2,'John Doe');Insert  intoStudentValues(3,'Harry');

The data in the current table is as follows:

　　

The name needs to be displayed in "Zhang San; John Doe; Harry" format.

Specifically through the implementation of the following:

Select Stuff (Select'; ' + name  from  FOR XML Path (")"),1,1,' as name '

At first glance, is not a little confused, not anxious, below us one by one.

The above statement is divided into two parts, the first to understand the use of the FOR XML path, in SQL Server we have a lot of methods to read the fragments in the XML field, in turn, SQL Server allows us to display the table data in XML, for XML Path is a way to display XML.

  Select '; ' +name from  Student FOR XML Path (")

This code tells the database server to stitch the name value in the generated XML into a ";" way.

For more information, refer to: http://www.cnblogs.com/doubleliang/archive/2011/07/06/2098775.html

Secondly, we understand the use of the stuff function, the content generated by the above-mentioned code is "; Zhang San; John Doe; Harry", obviously the first ";" is superfluous, so we remove it by stuff.

The STUFF (String,insert position,delete count,string inserted) function inserts a string into another string. When inserted, the inserted string may delete the specified number of characters.

The first parameter, string, refers to the content you want to manipulate, either as a fixed string or as a column;

The second parameter, insert position, refers to where the insertion begins, and SQL Server defaults to 1, rather than starting with 0;

The third parameter, delete count, refers to the number of characters to delete, the number specified from position, and if count is 0, it is not deleted;

The fourth argument, string inserted, represents the string to be inserted;

Eg:select stuff (' ABCDEFG ', 3, 2, ' 123 ')

Results: AB123EFG

SQL Server stitching a column of fields into a string method