SQLserver 根据条件将多行合并为一行

超、凢脫俗 2024-03-30 12:46 60阅读 0赞

将表和插入模拟数据

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 科目

![在这里插入图片描述][17cd045d53c649d4ab4874c8a38ffcd3.png]  
将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

![在这里插入图片描述][6b4fd0b6604046b9b33c36ae0ec35baf.png]  
sql:

> select ID,  
> name = (  
> stuff(  
> (select ‘,’ + course from \#TEST where ID = a.ID for xml path(‘’)),  
> 1,  
> 1,  
> ‘’  
> )  
> ) from \#TEST a group by ID

关于stuff函数的应用以及 for xml path的应用可参见这两篇文章：  
[FOR XML PATH][]  
[stuff函数][stuff]

[17cd045d53c649d4ab4874c8a38ffcd3.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/7f17e0c7bad14d339509d0b0cce0666f.png
[6b4fd0b6604046b9b33c36ae0ec35baf.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/c110ddc18e1f4ff7bba66e83c6bcf158.png
[FOR XML PATH]: https://blog.csdn.net/u012660464/article/details/128478484?csdn_share_tail=%7B%22type%22:%22blog%22,%22rType%22:%22article%22,%22rId%22:%22128478484%22,%22source%22:%22u012660464%22%7D
[stuff]: https://blog.csdn.net/u012660464/article/details/128478127?csdn_share_tail=%7B%22type%22:%22blog%22,%22rType%22:%22article%22,%22rId%22:%22128478127%22,%22source%22:%22u012660464%22%7D