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

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
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
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!

• Do you have zipcode data that includes latitude and longitude? Commented Feb 24, 2021 at 18:18
• There's quite a bit more to geolocation (as I've covered here on my blog) that needs to be configured before you get to concatenating the closest zip codes. Commented Feb 24, 2021 at 18:29
• Yes I do have a zip code file that includes that, I used your blog to do that too. Commented Feb 24, 2021 at 20:10
• I guess with that Adam, I am lost of how to take that query to find the surrounding zip codes for a particular zip code, not for a customer and a store.... Commented Feb 24, 2021 at 21:21
• The store DE is just a means to make the query work with a set of zip codes instead of one. And the query doesn't have to return just the closest zip -- you can remove the `where x.ranking = 1` at the end and it'll return all zips within the radius range. Commented Feb 25, 2021 at 13:41

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
``````