An instance of SQL server that combines a field value

The table is as follows:

Obtain the following results:

Solution

Use xml Conversion

The Code is as follows:

Copy codeThe Code is as follows: create table body

(

ID int,

BODY nvarchar (20)

)

Go

Insert into body VALUES (1, 'aaa ')

Insert into body VALUES (2, 'bbbbbb ')

Insert into body VALUES (1, 'cccccccc ')

Insert into body VALUES (3, 'ddddd ')

Go

SELECT * FROM body

SELECT distinct a. ID, stuff (SELECT ',' + body from body where id = a. id for xml path (''), 1, 1,'') ASBODY

FROM

Body

-- The specific idea is as follows:

SELECT ',' + body from body where id = a. id for xml path ('')

-- This statement is used to search for ID = a in the body table according to the ID in. all records of the ID, and convert it into an xml (you can refer to the article about converting the query set to xml)

Stuff (SELECT ',' + body from body where id = a. id for xml path (''), 1, 1 ,'')

-- This statement removes a comma before the generated xml and converts it to a scalar value.

-- Remove duplicate records with a distinct