Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about how to splice multiple lines of content in a column into one line in SQL Server. Many people may not know much about it. In order to make you understand better, the editor summarized the following content for you. I hope you can get something according to this article.

I encountered a SQL Server problem yesterday: I need to write a storage process to process the data in several tables. The final problem is that I want to concatenate the contents of multiple rows of a column in a table into one row, such as two columns of data in the table:

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

I want to change this table into the following format:

AAA Enterprise 1, Enterprise 2, Enterprise 3 BBB Enterprise 4, Enterprise 5

At first, I had a headache (I'm sure I don't have this kind of feeling if I know it. It doesn't have to be a headache (* ^ _ ^ *)). I found some information from the Internet, which is a relatively simple and convenient method. Now I will sum it up and give you a general summary. For everyone to learn.

The original table was named 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 dagger 1 dagger')) from Table_A as A group by category

Stuff and for xml path, which have been added since the SQL Server 2005 version, are used here. First talk about the role in the above sentence sql, and then explain the use of these two in detail.

The copy code is as follows: for xml path ('')

This sentence displays the obtained content in the form of XML.

The copy 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 the first "," to remove the spliced content.

Stuff:

1. Function

Stuff (param1, startIndex, length, param2) deletes length characters from startIndex (SQL starts with 1, not 0), and then replaces the deleted characters with param2.

2. Parameter param1: a character data expression. Param1 can be a constant, a variable, a character column, or a binary data column. StartIndex: an integer value that specifies the starting position of deletions and inserts. 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, delete up to the last character in param1. Length can be of type bigint. Param2, the return type. Returns character data if param1 is a supported character data type. Returns binary data if param1 is a supported binary data type.

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, some people may not know, in fact, it is the query result set in the form of XML presentation, with it we can simplify our query statements to achieve some of the previous may need to use the function live stored procedures to complete the work. Then give priority to an example.

Suppose there is a table containing the students' course choices (stu_courses):

Category name

Next, let's take a look at the query result statement that applies FOR XML PATH as follows:

The copy code is as follows: select stu_name,stu_course from stu_courses for xml path

After reading the above, do you have any further understanding of how to concatenate multiple lines of a column into one line in SQL Server? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.