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

Copy codeThe Code is as follows: -- 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 )'),
,
,
''), [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