八、分组合并字段

CREATE TABLE #MyTable(GroupField VARCHAR(50),MergeField VARCHAR(50))
	INSERT INTO #MyTable SELECT 'Hunan','ChangSha'
	INSERT INTO #MyTable SELECT 'Hunan','YueYang'
	INSERT INTO #MyTable SELECT 'GuangDong','GuangZhou'
	INSERT INTO #MyTable SELECT 'GuangDong','ShenZhen'

方式一

SELECT GroupField,MergeField =stuff(b.MergeField.value('/R[1]', 'nvarchar(max)'),1,1,'')
		FROM (SELECT GroupField FROM #MyTable GROUP BY GroupField) a CROSS apply (
        SELECT MergeField =(SELECT N',' + MergeField FROM #MyTable
              WHERE GroupField = a.GroupField FOR XML PATH(''), ROOT('R'), TYPE)
	) b;

方式二
SELECT GroupField,MergeField = stuff((SELECT ',' + MergeField 
		FROM #MyTable t WHERE GroupField = #MyTable.GroupField FOR XML PATH('')),1,1,'') 
		FROM #MyTable GROUP BY GroupField

方式三

CREATE FUNCTION Fn_GroupMerge
(
	@Field VARCHAR(50)
)
RETURNS VARCHAR(50)
AS
BEGIN
	DECLARE @Ret VARCHAR(8000)
	SET @Ret = ''
	SELECT @Ret = @Ret+';'+MergeField FROM #MyTable WHERE GroupField = @Field
	SET @Ret = stuff(@Ret,1,1,'')
	RETURN @Ret 
END
GO