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