多筆資料欄合併SQL Example

DECLARE @table TABLE (id nvarchar(4), memo nvarchar(4))
INSERT INTO @table (id, memo)VALUES ('1', 'm1');
INSERT INTO @table (id, memo)VALUES ('1', 'm2');
INSERT INTO @table (id, memo)VALUES ('2', 'm3');
INSERT INTO @table (id, memo)VALUES ('2', 'm4');
INSERT INTO @table (id, memo)VALUES ('3', 'm5');
INSERT INTO @table (id, memo)VALUES ('3', 'm6');

SELECT T1.id,
(
SELECT memo + ''
FROM @table T2
WHERE T2.id = T1.id
ORDER BY memo
FOR XML PATH('')
) AS memo
FROM @table T1
GROUP BY id