Implementation of SQL Server stitching a column of multiline content into one line

Implementation of SQL Server stitching a column of multiline content into one line

Submitted by: MDXY-DXY

This article mainly describes the SQL Server will be a column of multi-line content stitching into a row of the implementation method, the need for friends can refer to the following people first look at the sample code:

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:

?

1234567891011

select     类别,     名称 = (        stuff(            (select ‘,‘ + 名称 from Table_A where 类别 = A.类别 for xml path(‘‘)),            1,            1,            ‘‘        )    ) from Table_A as A group by 类别

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.

Copy CodeThe code is as follows:
FOR XML Path (")

The sentence is to display the resulting content in XML.

Copy CodeThe code is 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 the splicing content of the first "," remove

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

Example

?

1 2 3 4

select STUFF(‘abcdefg‘,1,0,‘1234‘)    --结果为‘1234abcdefg‘ select STUFF(‘abcdefg‘,1,1,‘1234‘)    --结果为‘1234bcdefg‘ select STUFF(‘abcdefg‘,2,1,‘1234‘)    --结果为‘a1234cdefg‘ select STUFF(‘abcdefg‘,2,2,‘1234‘)    --结果为‘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:

Copy CodeThe code is 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!

Implementation of SQL Server stitching a column of multiline content into one line