MSSQL 融合同列的多行记录

这个样子的一张表ID          NAME----------- ----------1           a1           b1           c1           d1           e2           f3           g4           h4           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 TGOcreate table t( ID INT, [NAME] NVARCHAR(10) )GOINSERT INTO tselect 1,'a' UNION ALLselect 1,'b' UNION ALLselect 1,'c' UNION ALLselect 1,'d' UNION ALLselect 1,'e' UNION ALLselect 2,'f' UNION ALLselect 3,'g' UNION ALLselect 4,'h' UNION ALLselect 4,'i'GOIF (OBJECT_ID('GET_NAMES')) IS NOT NULL DROP FUNCTION GET_NAMESGOCREATE FUNCTION GET_NAMES(@ID INT)RETURNS NVARCHAR(50)ASBEGINDECLARE @NAMES NVARCHAR(MAX) SET @NAMES=''SELECT @NAMES=@NAMES+[NAME]+',' FROM t WHERE ID=@IDRETURN @NAMESENDGO--第一种方法SELECT ID,dbo.GET_NAMES(ID) NAMES FROM t GROUP BY IDGO--第二种方法SELECT id,(SELECT [NAME]+',' FROM t t2 where t2.id=t.id group by id ,name FOR XML PATH(''))FROM t GROUP BY id