SQL Server: Merge multiple rows into one row and perform grouping statistics.

-- Create a test table and insert data

Create Table Test (Code varchar ( 50 ), [Values] varchar ( 10 ), [Count] Int )
Insert test select ' 001 ' , ' AA ' , 1
Union all select ' 001 ' , ' Bb ' , 2
Union all select ' 002 ' , ' Aaa ' , 4
Union all select ' 002 ' , ' Bbb ' , 5
Union all select ' 002 ' , ' CCC ' , 3 ;



-- Method 1
-- Merge multiple rows into one row and perform grouping statistics
Select Code,
[Values] =
Stuff (B. [values]. Value ( ' /R [1] ' , ' Nvarchar (max) ' ),
1 ,
1 ,
'' ), [Count]
From (Select Code, sum ([count]) As [Count]
From Test
Group by code)
Cross apply (
Select [values] = (
Select n ' , ' + [Values] from test
Where code = A. Code
For XML Path ( '' ), Root ( ' R ' ), Type
)
) B;



-- Method 2

--- The new solution in sql2005 uses XML

Select Code, Data = stuff (select ' , ' + [Values] from test t where code = t1.code for XML Path ( '' )), 1 , 1 , '' ), Sum ([count]) As [Count]
From test T1
Group by code



-- Query Result

-- 001 AA, BB 3
-- 002 Aaa, BBB, CCC 12



Drop Table Test