SQL Server: Classify and merge data rows in SQL

-- ========================================================== ============================================
-- Title: Classify and merge data rows in SQL
-- Author: dobear mail (MSN): dobear_0922@hotmail.com
-- Environment: Vista + sql2005
-- Date: 2008-04-22
-- ========================================================== ============================================

-- 1. Create a table and add Test Data
Create   Table TB (ID Int , [ Value ]   Varchar ( 10 ))
Insert TB Select   1 , ' AA '
Union   All   Select   1 , ' Bb '
Union   All   Select   2 , ' Aaa '
Union   All   Select   2 , ' Bbb '
Union   All   Select   2 , ' CCC '

-- Select * from TB
/**/ /*
ID value
---------------------
1 aa
1 bb
2 aaa
2 bbb
2 ccc

(5 row (s) affected)
*/


-- 2. Only user-defined functions can be used in SQL2000.
-- -- 2.1 create the merging function fn_strsum and merge value values based on ID
Go
Create   Function DBO. fn_strsum ( @ ID   Int )
Returns   Varchar ( 8000 )
As
Begin
Declare   @ Values   Varchar ( 8000 )
Set   @ Values   =   ''
Select   @ Values   =   @ Values   +   ' , '   + Value From TB Where ID = @ ID
Return   Stuff ( @ Values , 1 , 1 , '' )
End
Go

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

-- -- 2.2 create the merging function fn_strsum2 and merge value values based on ID
Go
Create   Function DBO. fn_strsum2 ( @ ID   Int )
Returns   Varchar ( 8000 )
As
Begin
Declare   @ Values   Varchar ( 8000 )
Select   @ Values   =   Isnull ( @ Values   +   ' , ' , '' ) + Value From TB Where ID = @ ID
Return   @ Values
End
Go

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


-- 3. New Solution in sql2005
-- -- 3.1 Use Outer apply
Select   *  
From ( Select   Distinct ID From TB) Outer Apply (
Select   [ Values ] =   Stuff ( Replace ( Replace (
(
Select Value From TB n
Where ID = A. ID
For XML auto
), ' <N value =" ' , ' , ' ), ' "/> ' , '' ), 1 , 1 , '' )
) N

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

-- 4. Delete the test table TB.
Drop   Table TB

/**/ /*
ID values
-------------------------------
1 aa, BB
2 AAA, BBB, CCC

(2 row (s) affected)
*/