Discussion on SQL Server stitching a column of multiline content into one line

• Example
• Stuff
• FOR XML Path
• Reference articles

Example

There was a problem with SQL Server yesterday: I had to write a stored procedure to process the data in several tables, and finally the problem was that I wanted to stitch up the rows of one column of a table into a row, such as a table with two columns of data:

category	name
Aaa	Enterprise 1
Aaa	Enterprise 2
Aaa	Enterprise 3
Bbb	Enterprise 4
Bbb	Enterprise 5

I want to make this table the following format:

category	name
Aaa	Enterprise 1, Enterprise 2, Enterprise 3
Bbb	Enterprise 4, Enterprise 5

A headache at first (will certainly not this feeling, not that must be a headache AH (*^__^*)), from the Internet to find a bit of information, is to find a relatively simple and convenient way, now generally summed up for everyone to learn together.

The original table name is table_a and the implementation code is as follows:

Select category, name = (Stuff ((select ', ' + name from table_a where category = A. Category for XML Path ("))," (), ")") from Table_a as A GROUP by Category

This uses the stuff and for XML paths that were added after the SQL Server 2005 version, first, the role of this SQL in the above, and then a detailed explanation of the use of the two.

The sentence is to display the resulting content in XML.

Stuff ((select ', ' + ep_name from ep_detail where ep_classes = a.ep_classes FOR XML Path (")), 1, 1, ')

This sentence is to put the splicing content of the first "," remove

Stuff:1, function

Stuff (param1, startIndex, length, param2)
Remove the length characters from the param1 in the startindex (all SQL starts with 1, not 0), and then replace the deleted characters with param2.

2. Parameters

• PARAM1: A character data expression. A param1 can be a constant, a variable, a character column, or a binary data row.
• StartIndex: An integer value that specifies the start position of the delete and insert. If startindex or length is negative, an empty string is returned. If startindex is longer than param1, an empty string is returned. StartIndex can be of type bigint.
• Length: An integer that specifies the number of characters to delete. If length is longer than param1, it is deleted to the last character in param1. Length can be a bigint type.
• Param2, returns the type. If PARAM1 is a supported character data type, character data is returned. If PARAM1 is a supported binary data type, binary data is returned.

Example

Select STUFF (' ABCDEFG ', 1,0, ' 1234 ')--       The result is ' 1234ABCDEFG '  select STUFF (' ABCDEFG ',, ' 1234 ')-       -the result is ' 1234BCDEFG '  Select STUFF (' ABCDEFG ', 2,1, ' 1234 ')       --Results for ' A1234CDEFG '  select STUFF (' ABCDEFG ', 2, 2, ' 1234 ')       --The result is ' A1234DEFG '

FOR XML Path

For XML Path Some people may know that some people may not know, in fact, it is the query result set is presented in XML form, with it we can simplify our query statement implementation of some previously may need to rely on the function of a live stored procedure to complete the work. Then take one instance as the main.

Suppose a table holds the student's elective Course (stu_courses):

Next, let's look at the query result statement that applies the for XML path as follows:

Select Stu_name,stu_course from stu_courses for XML path;

The results are as follows:

From this you can see that the FOR XML PATH can output query results according to the row into XML all kinds!

Reference articles

Discussion on SQL Server stitching a column of multiline content into one line