SQL Server concatenates multiple rows in a column into one row

Document directory

• Example
• Stuff:
• For xml path
• References

Example

Yesterday I encountered an SQL Server problem: I need to write a stored procedure to process data in several tables. The problem is that I want to splice multiple rows in one column of a table into one row, for example, a table has two columns of data:

Category	Name
AAA	Enterprise 1
AAA	Enterprise 2
AAA	Enterprise 3
BBB	Enterprise 4
BBB	Enterprise 5

I want to convert the table to the following format:

Category	Name
AAA	Enterprise 1, enterprise 2, enterprise 3
BBB	Enterprise 4 and enterprise 5

At the beginning, I had a headache. (If yes, I certainly don't feel this way. No, it must have been a headache (* ^__^ *). I found some information from the Internet, find a simple and convenient method. Let's summarize it for everyone to learn.

The original table name is Table_A. The implementation code is as follows:

Select category, name = (stuff (select ',' + name from Table_A where category =. category for xml path (''),'') from Table_A as A group by category

The stuff and for xml path added after SQL Server 2005 are used here. First, the roles in the preceding SQL statement are described, and then the usage of these two statements is described in detail.

for xml path('') 

This statement displays the obtained content in XML format.

stuff((select ',' + ep_name from ep_detail where ep_classes = a.ep_classes for xml path('')), 1, 1, '')

This sentence removes the first "," of the spliced content.

Stuff: 1. Role

Stuff (param1, startIndex, length, param2)
Start startIndex in param1 (SQL starts from 1 rather than 0), delete the length characters, and replace the deleted characters with param2.

2. Parameters

• Param1: A character data expression. Param1 can be a constant, variable, character column, or binary data column.
• StartIndex: an integer that specifies the start position of 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 of the bigint type.
• Length: an integer that specifies the number of characters to delete. If length is longer than param1, the last character in param1 is deleted at most. Length can be of the 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

Select STUFF ('abcdefg', 1234, '1234abcdefg') -- The result is '1234abcdefg' select STUFF ('abcdefg', 1234, '123 ') -- The result is '1234bcdefg' select STUFF ('abcdefg', 1234, '000000') -- The result is 'a1234cdefg' select STUFF ('abcdefg', 1234, '100 ') -- The result is 'a1234defg'

For xml path

For xml path some people may not know. In fact, it shows the query result set in XML format, with this feature, we can simplify our query statements to implement some work that may previously need to be completed through the function live storage process. Take an instance as the main type.

Suppose there is a table that stores Students' Course selections (stu_courses ):

Next, let's look at the Query Result Statement using the for xml path:

select stu_name,stu_course from stu_courses for xml path;

The result is as follows:

From this we can see that for xml path can output the query results into various XML types based on rows!

References

SQL Server concatenates multiple rows in a column into one row