SQL Server String Merge (merge) method summary

-- Method 1
-- Use the cursor method to merge strings.
-- Processed data
Create Table Tb (col1 varchar (10), col2 INT)
Insert TB select 'A', 1
Union all select 'A', 2
Union all select 'B', 1
Union all select 'B', 2
Union all select 'B', 3

-- Merge processing
-- Define result set table Variables
Declare @ t table (col1 varchar (10), col2 varchar (100 ))

-- Define and merge the cursor
Declare TB cursor local
For
Select col1, col2 from TB order by col1, col2
Declare @ col1_old varchar (10), @ col1 varchar (10), @ col2 int, @ s varchar (100)
Open TB
Fetch TB into @ col1, @ col2
Select @ col1_old = @ col1, @ s =''
While @ fetch_status = 0
Begin
If @ col1 = @ col1_old
Select @ s = @ s + ',' + Cast (@ col2 as varchar)
Else
Begin
Insert @ T values (@ col1_old, stuff (@ s ,''))
Select @ s = ',' + Cast (@ col2 as varchar), @ col1_old = @ col1
End
Fetch TB into @ col1, @ col2
End
Insert @ T values (@ col1_old, stuff (@ s ,''))
Close TB
Deallocate TB
-- Display results and delete test data
Select * From @ t
Drop table TB
/* -- Result
Col1 col2
---------------------
A 1, 2
B 1, 2, 3
--*/
Go

 

-- Method 2
-- Use user-defined functions and select functions to merge strings
-- Processed data
Create Table Tb (col1 varchar (10), col2 INT)
Insert TB select 'A', 1
Union all select 'A', 2
Union all select 'B', 1
Union all select 'B', 2
Union all select 'B', 3
Go

-- Merge processing functions
Create Function DBO. f_str (@ col1 varchar (10 ))
Returns varchar (100)
As
Begin
Declare @ Re varchar (100)
Set @ Re =''
Select @ Re = @ Re + ',' + Cast (col2 as varchar)
From TB
Where col1 = @ col1
Return (stuff (@ Re, 1,1 ,''))
End
Go

-- Call a function
Select col1, col2 = DBO. f_str (col1) from TB group by col1
-- Delete test
Drop table TB
Drop function f_str
/* -- Result
Col1 col2
---------------------
A 1, 2
B 1, 2, 3
--*/
Go

 

-- Method 3
-- Example of string merging using temporary tables
-- Processed data
Create Table Tb (col1 varchar (10), col2 INT)
Insert TB select 'A', 1
Union all select 'A', 2
Union all select 'B', 1
Union all select 'B', 2
Union all select 'B', 3

-- Merge processing
Select col1, col2 = cast (col2 as varchar (100 ))
Into # T from TB
Order by col1, col2
Declare @ col1 varchar (10), @ col2 varchar (100)
Update # t set
@ Col2 = case when @ col1 = col1 then @ col2 + ',' + col2 else col2 end,
@ Col1 = col1,
Col2 = @ col2
Select * from # T
/* -- Update the temporary table after processing
Col1 col2
-----------------------
A 1
A 1, 2
B 1
B 1, 2
B 1, 2, 3
--*/
-- Get the final result
Select col1, col2 = max (col2) from # T group by col1
/* -- Result
Col1 col2
---------------------
A 1, 2
B 1, 2, 3
--*/
-- Delete test
Drop table TB, # T
Go

 

Http://aierong.cnblogs.com/