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

a problem with SQL Server was encountered yesterday: a stored procedure was needed to process the data from several tables, and finally the problem was that I wanted to stitch a row of rows of one column of a table

For example, there are two columns of data in the table:

Ep_Classes ep_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:

Ep_Classes Ep_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: Ep_detail.

The implementation code is as follows:

Select Ep_classes, Ep_name = (Stuff ((select ', ' + ep_name from ep_detail where ep_classes =     a.ep_classes FOR XML Path ( "), ("), ")) from Ep_detail a group by ep_classes

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.

FOR XML Path (")

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 "," removed.

All right, now, let's get to the specifics. Usage:

①stuff:
2. Use
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. Ifstartindex or length is negative, then an empty string is returned. Ifparam1 long , an empty string is returned. startindex can be The bigint type.
Length
An integer that specifies the number of characters to delete. If length is greater than param1 long, delete up to param1. Length can be a bigint type.

3. Return 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.
4. Remarks
If the result value is greater than the maximum value supported by the return type, an error is generated.

eg

Select STUFF (' ABCDEFG ', 1,0, ' 1234 ')--       The result is ' 1234ABCDEFG ' select STUFF (' ABCDEFG ',, ' 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 '

Through the above 4 small examples, you should be able to understand the use of stuff.

②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.

We still introduce it through the following:

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:

<row>  <stu_name> Zhang San </stu_name>  <stu_course> Math </stu_course></row> <row>  <stu_name> Zhang San </stu_name>  <stu_course> language </stu_course></row> <row>  <stu_name> Zhang San </stu_name>  <stu_course> English </stu_course></row> <row>  <stu_name> John Doe </stu_name>  <stu_course> Math </stu_course></row> <row>  <stu_name> John Doe </stu_name>  <stu_course> language </stu_course></row>

From this you can see that the FOR XML PATH can output query results according to the row into XML all kinds! And we can also change the XML row node name, the code is as follows:

Select Stu_name,stu_course from Stu_courses for XML path (' Course ');

Look at the result, the original line node <row> becomes our custom name <course&gt in parentheses () after the path:

<course>  <stu_name> Zhang San </stu_name>  <stu_course> Math </stu_course></course ><course>  <stu_name> Zhang San </stu_name>  <stu_course> language </stu_course></ Course><course>  <stu_name> Zhang San </stu_name>  <stu_course> English </stu_course> </course><course>  <stu_name> John Doe </stu_name>  <stu_course> Math </stu_course ></course><course>  <stu_name> John Doe </stu_name>  <stu_course> language </stu_ Course></course>

In fact, we can also change the column node, also remember the column alias of the keyword as? Just use it! The code is as follows:

Select Stu_name as myname,stu_course as Mycourse from stu_courses for XML path (' Course ');

Show Results:

<course>  <MyName> Zhang San </MyName>  <MyCourse> Math </MyCourse></course>< Course>  <MyName> Zhang San </MyName>  <MyCourse> languages </MyCourse></course>< Course>  <MyName> Zhang San </MyName>  <MyCourse> English </MyCourse></course>< Course>  <MyName> John Doe </MyName>  <MyCourse> Math </MyCourse></course>< Course>  <MyName> John Doe </MyName>  <MyCourse> languages </MyCourse></course>

We can also build our favorite output way, see the code:

Select ' [' +stu_name+ ', ' +stu_course+ '] ' from the stu_courses for XML Path (');

Show Results:

[Zhang San, mathematics] [Zhang San, Chinese] [Zhang San, English] [John Doe, Mathematics] [John Doe, Chinese]

Well, with the instructions above, it is estimated that you will be able to understand the SQL statements in the starting question!

Of course, there are other workarounds for starting problems, such as cursors, custom functions, and so on, which you can add later.

The above is only for the purpose of discussion, if there are problems or other simple methods, I hope that we discuss the study together!

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