sql server 多行合并一行

1. 使用函数

go

CREATE FUNCTION dbo.fn_Sumtype(@type varchar(50))
RETURNS varchar(8000)
AS
BEGIN
DECLARE @values varchar(8000)
SET @values = ''
SELECT @values = @values + ',' + PRODUCT_TYPE FROM [DataCopy2] WHERE CUSTOMERFULLNAME=@type
RETURN STUFF(@values, 1, 1, '')
END
GO

 

测试如下：

 

SELECT CUSTOMERFULLNAME, data = dbo.fn_Sumtype(CUSTOMERFULLNAME) FROM [DataCopy2] GROUP BY CUSTOMERFULLNAME

 

2. 使用xml 技术

SELECT CUSTOMERFULLNAME, data=STUFF((SELECT ','+PRODUCT_TYPE FROM [DataCopy2] t WHERE CUSTOMERFULLNAME=t1.CUSTOMERFULLNAME FOR XML PATH('')), 1, 1, '')
FROM [DataCopy2] t1
GROUP BY CUSTOMERFULLNAME