[SQL Server] merge strings

This article reprinted: http://blog.csdn.net/leamonjxl/article/details/7042317

 

Useful post, favorites learning .......
/* Title: Merge strings by a field (simple merge)

Author: AI xinjue Luo. Xin Hua)
Time:
Location: Shenzhen, Guangdong Province

Description: merges the following data into the value field by ID.
ID value
-----------
1 aa
1 bb
2 aaa
2 bbb
2 ccc
Expected results:
ID value
-----------------
1 aa, BB
2 AAA, BBB, CCC
That is: group by ID, sum of values (string addition)
*/
-- 1. SQL2000 can only be solved using custom functions
Create Table TB (ID Int , Value Varchar ( 10 ))
Insert Into TB Values ( 1 , ' AA ' )
Insert Into TB Values ( 1 , ' Bb ' )
Insert Into TB Values ( 2 ,' Aaa ' )
Insert Into TB Values ( 2 , ' Bbb ' )
Insert Into TBValues ( 2 , ' CCC ' )
Go

Create Function DBO. f_str ( @ ID Int )
Returns Varchar ( 100 )
As
Begin
Declare @ Str Varchar ( 1000 )
Set @ Str = ''
Select @ Str = @ Str + ' , ' + Cast (Value As Varchar ) From TB Where ID = @ ID
Set @ Str = Right ( @ Str , Len ( @ Str ) - 1 )
Return @ Str
End
Go

-- Call a function
Select ID, Value = DBO. f_str (ID) From TB Group By ID

Drop Function DBO. f_str
Drop Table TB

-- 2. Methods in sql2005
Create Table TB (ID Int , Value Varchar ( 10 ))
Insert Into TB Values ( 1 , ' AA ' )
Insert Into TB Values ( 1 , ' Bb ' )
Insert Into TB Values ( 2 , ' Aaa ' )
Insert Into TB Values ( 2 ,' Bbb ' )
Insert Into TB Values ( 2 , ' CCC ' )
Go

Select ID, [ Value ] = Stuff (( Select ' , ' + [ Value ] From TB t Where ID = TB. ID For XML Path ( '' )), 1 , 1 , '' )
From TB
Group By ID

Drop Table TB

-- 3. Use a cursor to merge data
Create Table TB (ID Int , Value Varchar ( 10 ))
Insert Into TB Values ( 1 , ' AA ' )
Insert Into TB Values (1 , ' Bb ' )
Insert Into TB Values ( 2 , ' Aaa ' )
Insert Into TB Values ( 2 , ' Bbb ' )
Insert Into TB Values ( 2 , ' CCC ' )
Go
Declare @ T Table (ID Int , Value Varchar ( 100 )) -- Define result set table Variables
-- Define and merge the cursor
Declare My_cursor Cursor Local For
Select ID, Value From TB
Declare @ Id_old Int , @ ID Int , @ Value Varchar ( 10 ), @ S Varchar ( 100 )
Open My_cursor
Fetch My_cursorInto @ ID , @ Value
Select @ Id_old = @ ID , @ S = ''
While @ Fetch_status = 0
Begin
If @ ID = @ Id_old
Select @ S = @ S + ' , ' + Cast ( @ Value As Varchar )
Else
Begin
Insert @ T Values ( @ Id_old , Stuff ( @ S , 1 , 1 , '' ))
Select @ S = ' , ' + Cast ( @ Value As Varchar ),@ Id_old = @ ID
End
Fetch My_cursor Into @ ID , @ Value
End
Insert @ T Values ( @ Id_old , Stuff ( @ S , 1 , 1 , '' ))
Close My_cursor
Deallocate My_cursor

Select * From @ T
Drop Table TB