SQL column career three ways to compare

Merge column values
--*******************************************************************************************
Table structure with the following data:
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: Group by ID, sum of Value (string addition)

1. The old workaround (in SQL Server 2000 can only be resolved with a function.) )
--=============================================================================
CREATE table TB (ID int, value varchar (10))
INSERT into TB values (1, ' AA ')
INSERT into TB values (1, ' BB ')
INSERT into TB values (2, ' AAA ')
INSERT into TB values (2, ' BBB ')
INSERT into TB values (2, ' CCC ')
Go
--1. Creating a handler function
CREATE FUNCTION dbo.f_strunite (@id int)
RETURNS varchar (8000)
As
BEGIN
DECLARE @str varchar (8000)
SET @str = ' '
SELECT @str = @str + ', ' + value from TB Where[email protected]
RETURN STUFF (@str, 1, 1, ")
END
GO
--Call function
SELECt ID, value = dbo.f_strunite (ID) from the TB GROUP by ID
DROP table TB
Drop function Dbo.f_strunite
Go
/*
ID value
----------- -----------
1 AA,BB
2 AAA,BBB,CCC
(The number of rows affected is 2 rows)
*/
--===================================================================================
2. New workaround (resolves in SQL Server 2005 with outer apply, etc.). )
CREATE table TB (ID int, value varchar (10))
INSERT into TB values (1, ' AA ')
INSERT into TB values (1, ' BB ')
INSERT into TB values (2, ' AAA ')
INSERT into TB values (2, ' BBB ')
INSERT into TB values (2, ' CCC ')
Go
--Query processing
SELECT * FROM (select DISTINCT ID from TB) A OUTER APPLY (
SELECT value= STUFF (
REPLACE ((
SELECT value from TB N
WHERE n.id = a.id
For XML AUTO
), ', '), 1, 1, ') as P

DROP table TB

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

(2 rows affected)
*/

Method 2 in--sql2005
CREATE table TB (ID int, value varchar (10))
INSERT into TB values (1, ' AA ')
INSERT into TB values (1, ' BB ')
INSERT into TB values (2, ' AAA ')
INSERT into TB values (2, ' BBB ')
INSERT into TB values (2, ' CCC ')
Go

Select ID, [Values]=stuff ((SELECT ', ' +[value] from TB t where id=tb.id for Xmlpath (')), 1, 1, ')
From TB
GROUP BY ID

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

(2 row (s) affected)

*/

DROP table TB

SQL column career three ways to compare