一道sql小面试题_Sql Server

一道sql小面试题，求助
一个表，有Id和Name两个字段，其中Name可能会重复，问如何将重复Name的Id变成一个新的字段，也就是用逗号隔开的形式，并且Name不重复。

------解决方案--------------------

SQL code

DECLARE @t TABLE(id int, name varchar(10))INSERT @t SELECT 1, 'aa'UNION ALL SELECT 2, 'aa'UNION ALL SELECT 3, 'bb'UNION ALL SELECT 4, 'cc'UNION ALL SELECT 5, 'cc'-- 查询处理SELECT *FROM(    SELECT DISTINCT         name    FROM @t)AOUTER APPLY(    SELECT         [id]= STUFF(REPLACE(REPLACE(            (                SELECT id FROM @t N                WHERE name = A.name                FOR XML AUTO            ), '<N id="', ','), '"/>', ''), 1, 1, ''))N
------解决方案--------------------
既然是面试题
应该sql语句不是很长
SQL codeselect 1 as ID,'Name1Name1Name1' as Name INTO #temp  where 1=2INSERT INTO #temp Values(1,'Name1')INSERT INTO #temp Values(2,'Name2')INSERT INTO #temp Values(3,'Name3')INSERT INTO #temp Values(4,'Name4')INSERT INTO #temp Values(5,'Name5')INSERT INTO #temp Values(6,'Name6')INSERT INTO #temp Values(7,'Name7')INSERT INTO #temp Values(8,'Name8')INSERT INTO #temp Values(9,'Name9')INSERT INTO #temp Values(10,'Name10') INSERT INTO #temp Values(14,'Name2')INSERT INTO #temp Values(15,'Name3')INSERT INTO #temp Values(11,'Name5')INSERT INTO #temp Values(12,'Name6')INSERT INTO #temp Values(13,'Name7')INSERT INTO #temp Values(18,'Name7')INSERT INTO #temp Values(19,'Name7')INSERT INTO #temp Values(20,'Name7')select ID,Name,  (     select cast(ID as varchar(10))+';' from #temp  where Name=a.Name for xml path('')  ) as '我是新列' from #temp a  where not exists  (   select NULL from #temp b where a.ID>b.ID and a.Name=b.Name ) order by IDdrop table #temp--输出结果/******ID          Name            我是新列----------- --------------- --------------1           Name1           1;2           Name2           2;14;3           Name3           3;15;4           Name4           4;5           Name5           5;11;6           Name6           6;12;7           Name7           7;13;18;19;20;8           Name8           8;9           Name9           9;10          Name10          10;******/