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

效果：