SQLserver 多行合并为一行

将表和插入模拟数据

CREATE TABLE #TEST(
ID INT,
course VARCHAR(255)
)
INSERT INTO #TEST VALUES(1,'语文')
INSERT INTO #TEST VALUES(2,'数学')
INSERT INTO #TEST VALUES(3,'英语')
INSERT INTO #TEST VALUES(4,'英语')
INSERT INTO #TEST VALUES(4,'英语')
INSERT INTO #TEST VALUES(4,'英语1')

将course合并为一行

SELECT STUFF((
SELECT ',' +  course  FROM #TEST FOR XML PATH('')
),1,1,'') AS 科目

将course合并为一行后，给对应ID，并去掉重复

select ID, name = (stuff((select ',' + course from (SELECT distinct course,ID FROM #TEST) BB where ID =   
a.ID for xml path('')),1,1,'')) from #TEST a group by ID