SQL Server: Two ways to combine multiple lines into one row and do grouping statistics _mssql

Copy Code code as follows:

--Create test table, insert data

CREATE TABLE Test (code varchar (), [values] varchar (a), [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 One
--Combine multiple lines into one row and do grouped 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) a
CROSS Apply (
SELECT [Values] = (
SELECT N ', ' + [values] from test
WHERE Code = A.code
For XML PATH ('), ROOT (' R '), TYPE
)
) b;



--Method Two

A new solution to the---SQL2005 using 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 results

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



DROP TABLE Test