SQL SERVER merges duplicate rows and converts rows and columns

How to Implement oracle10g in SQL server2000
What is the function of wmsys. wm_concat?

And learn the following keywords:

I. Use of the row/column conversion keyword delimiter and unregister

Ii. OUTER APPLY

III. for xml auto and FOR XML
PATH

 

---- Turtle code ---
Merge column values
--*************************************** **************************************** ************
Table structure. The data is as follows:
Id
Value
-----
------
1
Aa
1
Bb
2
Aaa
2
Bbb
2
Ccc

Expected results:
Id values
------
-----------
1
Aa, bb
2
Aaa, bbb, ccc
That is: group by id, evaluate value
And (string addition)

1. The old solution (in SQL server
In 2000, only functions can be used .)
-- ===================================================== ==============================================
Create table tb (id int,
Value varchar (10 ))
Insert into tb values (1, 'A ')
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. Create a processing 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 id = @ id
Return stuff (@ str, 1, 1 ,'')
END
GO
-- Call a function
SELECt id,
Value = dbo. f_strUnite (id) FROM tb group by id
Drop table tb
Drop function dbo. f_strUnite
Go

-- ===================================================== ========================================================== ====
2. New Solution (using OUTER in SQL server 2005
APPLY .)
Create table tb (id int,
Value varchar (10 ))
Insert into tb values (1, 'A ')
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 [values] = STUFF (REPLACE (

(
SELECT value FROM tb
N
WHERE id = A. id
FOR XML AUTO

), '<N value = "', ','), '"/>', ''), 1, 1 ,'')
) N
Drop table tb

-- Method 2 in SQL2005
Create table tb (id int,
Value varchar (10 ))
Insert into tb values (1, 'A ')
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 xml path (''), 1, 1 ,'')
From tb
Group by id

Drop table tb