SQL Server records a field with the same field value. How can this problem be solved?

I. Problems

The table is as follows:

Obtain the following results:

Ii. solution 1

Use XML Conversion

The Code is as follows:

Create Table body (ID int, body nvarchar (20) goinsert into body values (1, 'aaa') insert into body values (2, 'bbbbb ') insert into body values (1, 'cccccccc') insert into body values (3, 'ddddd ') goselect * From bodyselect distinct. ID, stuff (select ',' + body from body where id =. ID for XML Path (''),'') as bodyfrombody a -- the specific idea is as follows: Select ',' + body from body where id =. ID for XML Path ('') -- this statement is used to find the id =. all records of the ID, and convert it into an XML (for articles about converting the query set to XML, there are a lot of blog gardens, You can see) stuff (select ',' + body from body where id =. ID for XML Path (''), 1, 1 ,'') -- The purpose of this statement is to remove a comma before the generated XML and convert it to a scalar value. -- finally, remove duplicate records with a distinct.

 

Solution 2

Using a cursor, I have defined a function here. You can also change it to a stored procedure

Use mytestgo -- the custom function returns the connected body (my database name is body) Create Function getresult (@ id int) returns nvarchar (20) based on the ID) asbegindeclare @ resultstr nvarchar (100), @ tempstr nvarchar (20) Declare @ tempid intdeclare mycursor cursorfor select * From bodyopen mycursorfetch next from mycursor into @ tempid, @ tempstrwhile (@ fetch_status = 0) beginif (@ tempid = @ ID) beginset @ tempstr = stuff (@ tempstr ,',') set @ resultstr = stuff (@ tempstr, @ resultstr) end fetch next from mycursor into @ tempid, @ tempstrendset @ resultstr = stuff (@ resultstr ,'') close mycursordeallocate mycursorreturn (@ resultstr) end -- after the function is created, run the following SQL statement select distinct M. ID, DBO. getresult (M. ID) as bodyfrombody as m

Iii. Description

The fields merged here are all strings. Next time I will discuss how to sum the integer.