MSSQL 融合同列的多行记录

这个样子的一张表
ID          NAME
----------- ----------
1           a
1           b
1           c
1           d
1           e
2           f
3           g
4           h
4           i

要一句SQL搞成下面的这个样子。

id          Names
----------- --------------------
1           a,b,c,d,e,
2           f,
3           g,
4           h,i,



IF(OBJECT_ID('t')) IS NOT NULL DROP TABLE T

GO

create table t
(
 ID INT,
 [NAME] NVARCHAR(10) 
)
GO

INSERT INTO t
select 1,'a' UNION ALL
select 1,'b' UNION ALL
select 1,'c' UNION ALL
select 1,'d' UNION ALL
select 1,'e' UNION ALL
select 2,'f' UNION ALL
select 3,'g' UNION ALL
select 4,'h' UNION ALL
select 4,'i'

GO

IF (OBJECT_ID('GET_NAMES')) IS NOT NULL DROP FUNCTION GET_NAMES
GO
CREATE FUNCTION GET_NAMES(@ID INT)
RETURNS NVARCHAR(50)
AS
BEGIN
DECLARE @NAMES NVARCHAR(MAX) SET @NAMES=''
SELECT @NAMES=@NAMES+[NAME]+',' FROM t WHERE ID=@ID
RETURN @NAMES
END

GO
--第一种方法
SELECT ID,dbo.GET_NAMES(ID) NAMES FROM t GROUP BY ID

GO
--第二种方法
SELECT id,
(SELECT [NAME]+',' FROM t t2 where t2.id=t.id group by id ,name FOR XML PATH(''))
FROM t GROUP BY id