-- 新建测试表
CREATE TABLE temp(id int, value varchar(10));
-- 插入测试数据
INSERT INTO temp VALUES(1, 'aa');
INSERT INTO temp VALUES(1, 'bb');
INSERT INTO temp VALUES(2, 'cc');
INSERT INTO temp VALUES(2, 'dd');
INSERT INTO temp VALUES(2, 'ff');
-- 查询,实现相同id的name以,分隔拼成字符串,id和value都为字段名称
SELECT id,
[value] = stuff((
SELECT ',' + [value]
FROM temp t
WHERE t.id = temp.id
FOR xml path('')) , 1 , 1 , '')
FROM temp
GROUP BY id;
数据库数据查询:
执行查询语句后的效果: