sqlserver分组将多行合并成一行

来源：互联网发布：mac 无法下载flash 编辑：程序博客网时间：2024/06/08 06:03

--创建测试表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'英语',100GOSELECT * 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

效果：

阅读全文

0 0