with Table1(type,col1)as( select 'A','A1' union all select 'A','A2' union all select 'B','B1' union all select 'B','B2') select distinct type, col1=stuff((select ';'+col1 from table1 where a.type=type for xml path('')),1,1,'') from table1 a
修改了一下,貌似这个执行效率要高些SELECT B.RepairListID,LEFT(ProList,LEN(ProList)-1) as list FROM ( SELECT RepairListID, (SELECT ProductID+';' FROM #temp WHERE RepairListID=A.RepairListID FOR XML PATH('')) AS ProList FROM #temp a GROUP BY RepairListID ) B
with Table1(type,col1)as(
select 'A','A1' union all
select 'A','A2' union all
select 'B','B1' union all
select 'B','B2')
select distinct type,
col1=stuff((select ';'+col1 from table1 where a.type=type for xml path('')),1,1,'') from table1 a
SELECT RepairListID,
(SELECT ProductID+';' FROM #temp
WHERE RepairListID=A.RepairListID
FOR XML PATH('')) AS ProList
FROM #temp a
GROUP BY RepairListID
) B