0

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!

5
  • 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

1 Answer 1

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .