---测试数据---
DECLARE @tb TABLE (
[ID] int,
[Name] varchar(1)
)
INSERT @tb
SELECT 1,'a'
UNION ALL SELECT 1, 'a'
UNION ALL SELECT 2, 'b'
UNION ALL SELECT 2, 'c'
UNION ALL SELECT 3, 'd'
UNION ALL SELECT 3, 'e'
---查询---
SELECT
ID,
name = STUFF(
(
SELECT ',' + name
FROM (SELECT DISTINCT ID, name FROM @tb) b
WHERE b.ID = a.ID
FOR xml PATH ('')
)
, 1
, 1
, '')
FROM @tb a
group by ID