sqlserver分组将多行合并成一行

--创建测试表 CREATE TABLE [dbo].[TestRows2Columns]( [Id] [int] IDENTITY(1,1) NOT NULL, [UserName] [nvarchar](50) NULL, [Subject] [nvarchar](50) NULL, [Source] [numeric](18,0) NULL ) GO --插入测试数据 INSERT INTO [TestRows2Columns] ([UserName],[Subject],[Source]) SELECT N'张三',N'语文',60 UNION ALL SELECT N'李四',N'数学',70 UNION ALL SELECT N'王五',N'英语',80 UNION ALL SELECT N'王五',N'数学',75 UNION ALL SELECT N'王五',N'语文',57 UNION ALL SELECT N'李四',N'语文',80 UNION ALL SELECT N'张三',N'英语',100 GO SELECT * FROM [TestRows2Columns]

一、分组合并多行：

SELECT B.UserName,(SELECT RTRIM( A.[Subject]) +''+QUOTENAME(A.Source) +',' FROM TestRows2Columns A   WHERE A.UserName=B.UserName FOR XML PATH ('')) AS DDDD  FROM TestRows2Columns B  GROUP BY UserName 

注释：RTRM函数是去除右边的空格；QUOTENAM函数给字段套个字符，默认套个[];

效果：

二、单列多行合成一行

select( SELECT RTRIM(UserName) + ',' FROM TestRows2Columns  FOR XML PATH ('')  ) as ddd

效果：