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