Finding all the zip codes in a radius with all zip codes in the same field

Question

I am trying to build out an way to return all of the surrounding zip codes for a particular one in a single field, so that I can use it for additional queries.

I found this on the Salesforce Marketing Cloud Dev site for SQL:

SELECT zip
, city
,  ROUND(6378.137 * ACOS(     
                  CASE       
                    WHEN (SIN(RADIANS((SELECT latitude FROM [ZipCode] where zip = 33308))) * SIN(RADIANS(geo.Latitude))) + (COS(RADIANS((SELECT latitude FROM [ZipCode] where zip = 33308))) * COS(RADIANS(geo.Latitude)) * COS(RADIANS(geo.Longitude) - RADIANS((SELECT longitude FROM [ZipCode] where zip = 33308)))) > 1 THEN 1       
                    WHEN (SIN(RADIANS((SELECT latitude FROM [ZipCode] where zip = 33308))) * SIN(RADIANS(geo.Latitude))) + (COS(RADIANS((SELECT latitude FROM [ZipCode] where zip = 33308))) * COS(RADIANS(geo.Latitude)) * COS(RADIANS(geo.Longitude) - RADIANS((SELECT longitude FROM [ZipCode] where zip = 33308)))) < -1 THEN -1       
                    ELSE (SIN(RADIANS((SELECT latitude FROM [ZipCode] where zip = 33308))) * SIN(RADIANS(geo.Latitude))) + (COS(RADIANS((SELECT latitude FROM [ZipCode] where zip = 33308))) * COS(RADIANS(geo.Latitude)) * COS(RADIANS(geo.Longitude) - RADIANS((SELECT longitude FROM [ZipCode] where zip = 33308))))     
                  END
              ),0
) AS Distance 
FROM [ZipCode] AS geo 
WHERE   
ROUND(6378.137 * ACOS(     
                  CASE       
                    WHEN (SIN(RADIANS((SELECT latitude FROM [ZipCode] where zip = 33308))) * SIN(RADIANS(geo.Latitude))) + (COS(RADIANS((SELECT latitude FROM [ZipCode] where zip = 33308))) * COS(RADIANS(geo.Latitude)) * COS(RADIANS(geo.Longitude) - RADIANS((SELECT longitude FROM [ZipCode] where zip = 33308)))) > 1 THEN 1       
                    WHEN (SIN(RADIANS((SELECT latitude FROM [ZipCode] where zip = 33308))) * SIN(RADIANS(geo.Latitude))) + (COS(RADIANS((SELECT latitude FROM [ZipCode] where zip = 33308))) * COS(RADIANS(geo.Latitude)) * COS(RADIANS(geo.Longitude) - RADIANS((SELECT longitude FROM [ZipCode] where zip = 33308)))) < -1 THEN -1       
                    ELSE (SIN(RADIANS((SELECT latitude FROM [ZipCode] where zip = 33308))) * SIN(RADIANS(geo.Latitude))) + (COS(RADIANS((SELECT latitude FROM [ZipCode] where zip = 33308))) * COS(RADIANS(geo.Latitude)) * COS(RADIANS(geo.Longitude) - RADIANS((SELECT longitude FROM [ZipCode] where zip = 33308))))     
                  END
              ),0
) <= 32.1869

What it is doing is sending each of the 90 surrounding zips are populated on their own row. What I am looking for is to put the zips together. See the examples below:

Current
12345
23456
34567
45678
56789

What I want is:
12345, 23456, 34567, 45678, 56789

Thanks for any help anyone can give!

Answer 1

Since it appears your question isn't as much about geolocation as it is concatenating rows values into a single field value, here's an example adapted from one of my previous answers.

Assuming the results of your geolocation query looks like this:

OriginZipCode  ZipCode
------------   -------
12345          55555  
12345          66666  
12345          77777
22222          11223
22222          22334

A query like this:

SELECT distinct
  cz.OriginZipCode
, STUFF(x.ClosestZipCodes,1,1,'') ClosestZipCodes
FROM [ClosestZipCodes] AS cz
CROSS APPLY (
    SELECT ',' + rtrim(cz0.ZipCode)
    FROM [ClosestZipCodes] AS cz0
    WHERE cz0.OriginZipCode = cz.OriginZipCode
    FOR XML PATH('') 
) x (ClosestZipCodes)

Would produce results like this:

OriginZipCode  ClosestZipCodes
------------   -----------------
12345          55555,66666,77777  
22222          11223,22334