实现效果
原始数据
原始表
转换后数据
转换后表
示例代码
/****** 删除表******/
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
