SELECT popdb_id AS POPDB_id,Property
-- INTO #temp
FROM(
SELECT distinct cpp.client_id,cppv.popdb_id
FROM dbo.a cpp
INNER JOIN b cppv ON cpp.property_id = cppv.property_id
INNER JOIN c cp ON cp.popdb_id = cppv.popdb_id
) ppm
OUTER APPLY(
SELECT Property= STUFF(REPLACE(REPLACE(
(
SELECT Property
FROM dbo.a N
INNER JOIN b cppv ON N.property_id = cppv.property_id
WHERE cppv.POPDB_id = ppm.popdb_id
ORDER BY N.Property_type
-- INTO #temp
FROM(
SELECT distinct cpp.client_id,cppv.popdb_id
FROM dbo.a cpp
INNER JOIN b cppv ON cpp.property_id = cppv.property_id
INNER JOIN c cp ON cp.popdb_id = cppv.popdb_id
) ppm
OUTER APPLY(
SELECT Property= STUFF(REPLACE(REPLACE(
(
SELECT Property
FROM dbo.a N
INNER JOIN b cppv ON N.property_id = cppv.property_id
WHERE cppv.POPDB_id = ppm.popdb_id
ORDER BY N.Property_type