Multiple rows of SQL query merged into one line

Problem Description:
Neither in SQL 2000, nor in SQL 2005, does the aggregate function of the string be provided,
Therefore, when we are dealing with the following requirements, it will be more troublesome:
There are table TB, as follows:
ID value
----- ------
1 AA
1 BB
2 AAA
2 BBB
2 CCC
Need to get results:
ID values
------ -----------
1 AA,BB
2 AAA,BBB,CCC
That is, the group by ID, the sum of the value (string addition)

1. The old solution

--1. Creating a handler function
CREATE FUNCTION dbo.f_str (@id int)
RETURNS varchar (8000)
As
BEGIN
DECLARE @r varchar (8000)
SET @r = "
SELECT @r = @r + ', ' + value
From TB
WHERE [email protected]
RETURN STUFF (@r, 1, 1, ")
END
GO
--Call function

SELECt ID, values=dbo.f_str (ID)
From TB
GROUP by ID

--2. A new solution
--Sample data
DECLARE @t TABLE (id int, value varchar (10))
INSERT @t SELECT 1, ' AA '
UNION all SELECT 1, ' BB '
UNION all SELECT 2, ' AAA '
UNION all SELECT 2, ' BBB '
UNION all SELECT 2, ' CCC '

--Query processing
SELECT *
From (
SELECT DISTINCT
Id
From @t
) A
OUTER APPLY (
SELECT
[Values]= STUFF (replace (replace (
(
SELECT value from @t N
WHERE id = a.id
For XML AUTO
), ' <n value= ' ', ', '), '/> ', ', 1, 1, ')
) N

/*--Results
ID values
----------- ----------------
1 AA,BB
2 AAA,BBB,CCC
(2 rows affected)
--*/

Multiple rows of SQL query merged into one line