Combine strings according to a field value--SQL

The process of doing the project, encountered a problem, the sameID, the record of different value want to merge into a record,value is separated by comma, search the solution from the net, tidy up as follows, Memo.

First, string Merge

Table Name: Test

Field:

ID int

Name nvarchar (50)

Field value:

Expected Result:

ID NAMESTR

-----------------------------

1 a,b,c

2 D,e

3 F

SQL implementation:

1. using functions to solve

--Create a handler function

CREATE FUNCTION dbo.f_str (@id int)

RETURNS varchar (8000)

As

BEGIN

DECLARE @r varchar (8000)

SET @r = "

SELECT @r = @r + ', ' + [name]

from [Test]

WHERE [Id][email protected]

RETURN STUFF (@r, 1, 1, ")

END

GO

--Call function

SELECT [id], [namestr]=dbo.f_str (ID)

From [test]

GROUP by [id]

Operation Result:

2. directly with SQL Solve

SELECT *

From(

SELECT DISTINCT [id]

from [Test]

)A

OUTER APPLY (

SELECT

[Namestr]= STUFF (replace (replace (

(

SELECT [name] from [test] N

WHERE [id] = a.id

For XML AUTO

), ' <n name= ' ', ', '), '/> ', ', 1, 1, ')

)M

Operation Result:

Do not understand this SQL does not matter, the following detailed analysis

Second, Detailed Analysis

1, STUFF

Role:

The stuff function inserts a string into another string. It is deleted from the starting position in the first string

A character that specifies the length, and then inserts the second string at the beginning of the first string.

Grammar:

STUFF (character_expression, start, length, character_expression)

Parameters:

character_expression

A character data expression. character_expression can be constants, variables, or

To be a character column or a binary data row.

Start

An integer value that specifies the start position of the delete and insert. If start or length is negative,

The empty string is returned. If start is longer than the first character_expression , the return

The go home string. start can be a bigint type.

length

An integer that specifies the number of characters to delete. If length is greater than the first one

character_expression Long, remove up to the first character_expression

The last character in the. length can be a bigint type.

return type:

If character_expression is a supported character data type, character data is returned.

Returns two if character_expression is a supported binary data type

Binary data.

Comments:

If the start position or length value is negative, or if the start position is longer than the first string

Returns an empty string. If the length to be removed is greater than the length of the first string, the deletion

The last character in the string except to the first one. If the result value is greater than the return type supports the most

Large value, an error is generated.

Example:

SELECT STUFF (' abcdef ', 2, 3, ' ijklmn ');

GO

Results:

SELECT STUFF (' abcdef ',-2, 3, ' ijklmn ');

GO

Results:

SELECT STUFF (' abcdef ', 2, ten, ' ijklmn ');

GO

Results:

2. Cross apply and OUTER apply

Use the APPLY operator to invoke a table-valued function for each row returned by an external table expression that implements a query operation. The table-valued function acts as the right input and the outer table expression as the left input. The resulting row is combined as the final output by evaluating the right input for each row in the left input. The list of columns generated by the APPLY operator is the column set in the left input, followed by the list of columns returned by the right input.

There are two forms of apply: Crossapply and OUTER apply. Cross APPLY returns only the rows in the external table that generated the result set through the table-valued function. OUTER APPLY Returns both the row that generated the result set and the row that does not produce the result set, where the value in the column generated by the table-valued function is NULL.

Use examples to differentiate between the two:

First sheet:

Table name:student

Field Name:

Field value:

Second sheet:

Table name:student_class

Field Name:

Field value:

Cross APPLY

SELECT *

From [student] a

Cross APPLY (

SELECT [Class], [score]

from [Student_class]

WHERE student_id = a.id

) b

Operation Result:

OUTER APPLY

SELECT *

From [student] a

OUTER APPLY (

SELECT [Class], [score]

from [Student_class]

WHERE student_id = a.id

) b

Operation Result:

3. for XML AUTO

Convert the result to an XML structure, for example , in the student table above 2, execute the following statement

SELECT *

From [student]

For XML AUTO

Operation Result:

Combine strings according to a field value--SQL