The merge field is the reverse process of the split field processing, which will be solved for the instance. Question: Merge the Name field with the same ID number. Original table Table1 (ID INT, Name Varchar (100))
ID Name ----------- 1 'A'1' b'1 'c'2' e'2 'f'
I hope to get the result:
ID Name ------------------ 1 'A, B, C'2' E, F '
First, use the most original method to solve, that is, by cycling, connect the Name field when there is the same ID number, otherwise insert a new record, this method is clumsy, does not recommend this method, process As follows: Create Table Table1 (ID INT, Name Varchar (100)) Insert Table1 (ID, Name) SELECT 1, 'A'Union All Select 1,' B'Union All Select 1, 'C'Union All Select 2,' D'Union All Select 2, 'E'Union All SELECT 3,' F'UNION All SELECT 3, 'G'
Declare @ID int, @ name var, name #b (id int, name varchar (100)) Declare a_cursor cursor for select id, namefrom table1order by id open a_cursor
Fetch next from a_cursor INTO @ ID, @ name
While @@ fetch_status = 0begin if exists (SELECT 1 from #b WHERE ID = @ id) begin update #b set name = name ',' @ name where id = @ id end else insert # (id, name) SELECT @ id, @ name
Fetch next from a_cursor @ID, @nameendclose a_cursordeallocate a_cursor
Select * from #bdrop Table # b, table1
Second, use the merge function. (This method applies to SQL2000 version or more) Create Table Table1 (ID INT, Name Varchar (100)) Insert Table1 (ID, Name) Select 1, 'A'Union All Select 1,' B'Union All Select 1, 'C 'Union All Select 2,' D'Union All Select 2, 'E'Union All SELECT 3,' F'UNION All SELECT 3, 'G'
Create function dbo.fn_b (@a varchar (10)) Returns varchar (1000) as begindeclare @s varchar (1000) set @s = '' SELECT @ s = @ S Name from table1 where id = @ areturn (@S) ) Endselect A, B = dbo.fn_b (id) from table1 group by a
Drop Table1
Analysis: This method uses functions, simple and easy to understand, more common, but cannot be used below SQL2000.
Third, use a specific sort plus UPDATE statement to implement. This method can also be used in a lower than SQL2000 versions. Declare @id int, @namelist varchar (100) Create Table #a (ID INT, Name Varchar (100), Namelist Varchar (100)) Create Table Table1 (ID INT, Name Varchar (100)) Insert Table1 (ID, Name ) SELECT 1, 'A'Union All Select 1,' B'Union All Select 1, 'C'Union All Select 2,' D'Union All Select 2, 'E'Union All SELECT 3,' F'UNION All SELECT 3, 'G'Insert #a (ID, name) Select * from table1 order by id - When inserted here, you must press the ID to first sort Update #aset namelist = @ Namelist, @ namelist = (Case When ID = @ ID Then @NameList ',' Name else name end, @ id = id from #a
SELECT ID, NAME = max (Namelist) from #a group by iddrop table #adrop table table1 parsing: This method uses a temporary table using the modified statement and the packet query, and it is not the second in efficiency and complexity. Method, but can be used in SQL2000 below.
