Example of merging a field value in SQL Server _mssql

The table is as follows:

How to get the following results:

Solution

Using XML transformations

The code is as follows:

Copy Code code as follows:

CREATE TABLE Body

(

ID int,

Body nvarchar (20)

)

Go

INSERT into the body VALUES (1, ' AAAA ')

INSERT into the body VALUES (2, ' bbbb ')

INSERT into the body VALUES (1, ' CCCCCC ')

INSERT into the body VALUES (3, ' ddddd ')

Go

SELECT * from body

SELECT DISTINCT A.id,stuff (SELECT, ' +body from the body WHERE id=a.id for XML Path ('), 1,1, ') asbody

From

Body A

--The concrete idea is this:

SELECT ', ' +body from the body WHERE id=a.id for XML Path (')

--The purpose of this statement is to look up all the records in the body table by the ID in a, and convert it into an XML (an article about converting a query set into XML, you can see) id=a.id

Stuff (SELECT ', ' +body from the body WHERE id=a.id for XML Path (')), 1, 1, ' "

The function of this statement is to remove and convert a comma in front of the generated XML into a scalar value

--end with a distinct to remove duplicate records