SQL Server implements methods for stitching a column of multiline content into one line _mssql

Now let's look at the sample code:

Example

A problem with SQL Server was encountered yesterday: you need to write a stored procedure to handle the data in several tables, and the final problem is that I want to stitch together multiple rows of one column of a table, such as two columns of data in a table:

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

I want to change this form into the following format:

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

At first very headache (will certainly not this feeling, not that must be a headache AH (*^__^*)), from the Internet to find a point of information, is to find a relatively simple and convenient method, now roughly 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 (')),
			1,
			1,
    '
		)
	) from 
table_a as A Group by category

Here, using the SQL Server 2005 version of stuff and for XML path, let's say the role in the SQL above, and then explain the two uses in more detail.

Copy Code code as follows:

FOR XML Path (')

This sentence is to display the resulting content in the form of XML.

Copy Code code as follows:

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

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

Stuff

1, the role

Stuff (param1, startIndex, length, param2)
Removes the length character from startindex (in SQL, starting from 1 instead of 0), and then replaces the deleted character with param2 with the param1.

2. Parameter
param1: A character data expression. A param1 can be a constant, a variable, or a character or binary data column.
startindex: An integer value that specifies the start position of the deletion and insertion. 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 a bigint type.
Length: An integer that specifies the number of characters to delete. If length is longer than param1, delete up to the last character in param1. Length can be a bigint type.
param2, return type. If the param1 is a supported character data type, the character data is returned. If PARAM1 is a supported binary data type, the binary data is returned.

Example

Select STUFF (' ABCDEFG ', 1,0, ' 1234 ')-    -the result is ' 1234ABCDEFG ' 
select STUFF (' ABCDEFG ', 1,1, ' 1234 ')    -- The result is ' 1234BCDEFG ' 
select STUFF (' ABCDEFG ', 2,1, ' 1234 ')    --The result is ' 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 in the form of XML, with which we can simplify our query to implement some of the work that might previously need to be done with the help of a function live stored procedure. Then take one instance as the main.

Suppose you have a table that holds the student's Elective (stu_courses):

Next we look at the query result statement that applies for XML path as follows:

Copy Code code as follows:

Select Stu_name,stu_course from stu_courses for XML path;

The results are as follows:

You can see for XML PATH that the query results can be exported to all kinds of XML based on rows!