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

Note: This article is for sharing only, the copyright belongs to the original author.

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:

1. Select Ep_classes, Ep_name = (Stuff ((select ', ' + ep_name from ep_detail where ep_classes =
2. 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.

1. FOR XML Path (")

The sentence is to display the resulting content in XML.

[SQL]View PlainCopy

1. 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:
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.

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

1. select stuff ( ' 1234 ')        --result is ' 1234ABCDEFG '   
2. select stuff ( ' ABCDEFG ', 1,1, ' 1234 ')         --result is ' 1234BCDEFG '   
3. select stuff ( ' ABCDEFG ', 2, 1, ' 1234 ')        --result for ' A1234CDEFG '   
4. 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:

1. Select Stu_name,stu_course from stu_courses for XML path;

The results are as follows:

2. <row>
4. <stu_name> Zhang San </stu_name>
6. <stu_course> Math </stu_course>
8. </row>
10. <row>
12. <stu_name> Zhang San </stu_name>
14. <stu_course> Chinese </stu_course>
16. </row>
18. <row>
20. <stu_name> Zhang San </stu_name>
22. <stu_course> English </stu_course>
24. </row>
26. <row>
28. <stu_name> John Doe </stu_name>
30. <stu_course> Math </stu_course>
32. </row>
34. <row>
36. <stu_name> John Doe </stu_name>
38. <stu_course> Chinese </stu_course>
40. </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:

1. 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> in parentheses () after the path:

2. <course>
4. <stu_name> Zhang San </stu_name>
6. <stu_course> Math </stu_course>
8. </Course>
10. <course>
12. <stu_name> Zhang San </stu_name>
14. <stu_course> Chinese </stu_course>
16. </Course>
18. <course>
20. <stu_name> Zhang San </stu_name>
22. <stu_course> English </stu_course>
24. </Course>
26. <course>
28. <stu_name> John Doe </stu_name>
30. <stu_course> Math </stu_course>
32. </Course>
34. <course>
36. <stu_name> John Doe </stu_name>
38. <stu_course> Chinese </stu_course>
40. </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:

1. Select Stu_name as myname,stu_course as Mycourse from stu_courses for XML Path (' course ');

Show Results:

2. <course>
4. <MyName> Zhang San </MyName>
6. <mycourse> Math </mycourse>
8. </Course>
10. <course>
12. <MyName> Zhang San </MyName>
14. <mycourse> Chinese </mycourse>
16. </Course>
18. <course>
20. <MyName> Zhang San </MyName>
22. <mycourse> English </mycourse>
24. </Course>
26. <course>
28. <MyName> John Doe </MyName>
30. <mycourse> Math </mycourse>
32. </Course>
34. <course>
36. <MyName> John Doe </MyName>
38. <mycourse> Chinese </mycourse>
40. </Course>

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

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

Show Results:

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

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