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
)A
OUTER 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 code


select 1 as ID,'Name1Name1Name1' as Name INTO #temp  where 1=2

INSERT 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 ID
drop 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;
******/