concate string when group by

--Prepare test data
CREATE TABLE TblTest
(
	GroupName varchar(max),
	MemberName varchar(max)
)

INSERT INTO
	TblTest
VALUES
	(
		'g1',
		'm1'
	)
INSERT INTO
	TblTest
VALUES
	(
		'g1',
		'm2'
	)
INSERT INTO
	TblTest
VALUES
	(
		'g2',
		'm3'
	)
INSERT INTO
	TblTest
VALUES
	(
		'g2',
		'm4'
	)
SELECT
	*
FROM
	TblTest




--drop temptable
IF Object_ID('tempdb..#tmpTest') IS NOT NULL
	BEGIN
		DROP TABLE #tmpTest
	END
	
SELECT
	TblTest1.GroupName,
	( 
		SELECT  
			TblTest2.MemberName + ';' 
		FROM 
			TblTest  TblTest2
		WHERE 
			TblTest2.GroupName = TblTest1.GroupName 
		ORDER BY 
			TblTest2.MemberName
		FOR XML PATH
			('') 
	) AS Resources
INTO
	#tmpTest
FROM
	TblTest TblTest1
GROUP BY
	TblTest1.GroupName
	
SELECT
	*
FROM
	#tmpTest