-- *** Test Data ***
CREATE TABLE #t
(
	Property_id int NOT NULL
	,Email varchar(255) NULL
)
INSERT INTO #t
SELECT 1, 'a@b.com'
UNION ALL SELECT 2, 'a@b.com'
UNION ALL SELECT 3, 'a@b.com'
UNION ALL SELECT 4, 'a@b.com'
UNION ALL SELECT 5, 'a@b.com'
UNION ALL SELECT 6, 'c@d.com'
UNION ALL SELECT 7, 'c@d.com'
UNION ALL SELECT 8, 'c@d.com'
-- *** End Test Data ***

;WITH cte
AS
(
	SELECT A.Email
			,STUFF(
					(SELECT ' ' + CAST(B.Property_id AS varchar(20)) + ','
					  FROM #t B
					  WHERE A.EMail = B.Email
					  FOR XML PATH('') )
					,1
					,1
					,'') AS Property_IDs
	FROM #t A
	GROUP BY A.Email
)
SELECT EMail, LEFT(Property_IDs, LEN(Property_IDs) - 1) AS Property_IDs
FROM cte

SELECT
	em.[email] AS [Email]
	, LEFT(ids.[ids], LEN(ids.[ids]) - 1) AS [Ids]
FROM
	(
	SELECT DISTINCT [email] FROM #t
	)
	AS em

	CROSS APPLY (
		SELECT CAST(t.[Property_ID] AS VARCHAR(10)) + ','
		FROM #t AS t
		WHERE t.[email] = em.[email]
		FOR XML PATH('')
		)
		AS ids ([ids])