Sql 行合并

实现效果

原始数据

原始表

转换后数据

转换后表

示例代码

/****** 删除表******/
IF EXISTS  (SELECT  * FROM dbo.SysObjects WHERE ID = object_id(N'[MergeRow]') AND OBJECTPROPERTY(ID, 'IsTable') = 1) 
drop table MergeRow

/****** 新建测试表，插入测试数据******/
CREATE TABLE MergeRow(id int, column1 varchar(100), column2 varchar(100)) 
INSERT MergeRow SELECT 1, '行1列1','行1列2' 
UNION ALL SELECT 1, '行2列1','行2列2' 
UNION ALL SELECT 2, '行3列1','行3列2' 
UNION ALL SELECT 2, '行4列1','行4列2' 
UNION ALL SELECT 2, '行5列1','行5列2' 

/****** 查询转换前原始表 ******/
SELECT * FROM MergeRow 

/*方法一关键字function*/ 
/****** 创建自定义方法，合并列一******/
GO 
CREATE function dbo.fn_RowSumcolumn1(@id int) 
RETURNS varchar(8000)
AS 
BEGIN 
DECLARE @column1 varchar(8000)
SELECT @column1 = isnull(@column1 + '，', '') + column1 FROM MergeRow WHERE id=@id 
RETURN @column1
END 
GO 
/****** 创建自定义方法，合并列二******/
GO
CREATE function dbo.fn_RowSumcolumn2(@id int) 
RETURNS varchar(8000)
AS 
BEGIN 
DECLARE @column2 varchar(8000)
SELECT @column2 = isnull(column2 + '，', '') + column2 FROM MergeRow WHERE id=@id 
RETURN @column2
END 
GO

/****** 调用方法，查看合并后数据******/
SELECT id, column1 = dbo.fn_RowSumcolumn1(id),column2 = dbo.fn_RowSumcolumn2(id) FROM MergeRow GROUP BY id 
/****** 删除方法******/
DROP function dbo.fn_RowSumcolumn1 
DROP function dbo.fn_RowSumcolumn2

/*方法二关键字XML*/ 
SELECT id, column1=STUFF((SELECT '，'+column1 FROM MergeRow t WHERE id=MergeRow.id FOR XML PATH('')), 1, 1, '') 
,[values1]=STUFF((SELECT '，'+column2 FROM MergeRow t WHERE id=MergeRow.id FOR XML PATH('')), 1, 1, '') 
FROM MergeRow 
GROUP BY id