Concatenate SQL Server column values into a single, comma-delimited value

Our vendors can be licensed in one or more states. Until today, our search results only showed the state in which the vendor lived. Now, the search results show the state in which they live and the (most often) neighboring states they also cover (if applicable).

Technically, we're saving the vendor's locations (county/city and state) in a table. So it's one vendor to zero or more locations (plus the "home" state in which they live, which is their mailing address).

Our employees search for vendors based on a specific location. Using a radius search based on lat/long coordinates, I return a list of vendors. As mentioned, one column is the state (ex: OH).

Well, some vendors near Cincinnati, OH cover locations in Kentucky (KY) and Indian (IN). So...I wanted to show, as an example, the following if the vendor covered locations in those states:

OH, KY, IN

Maybe there's a better way, but I came across "solution 4" on this site, which lead me to create the following function (the "magic" happens with the bold line):

ALTER FUNCTION [dbo].[fx_GetStatesCovered]
 (
 @VendorID int
 )
RETURNS varchar(max)
AS
 BEGIN

 DECLARE @StatesCovered char(2)

 SELECT 
  @StatesCovered = States.Abbr
 FROM 
  Vendors WITH (NOLOCK)
  JOIN States WITH (NOLOCK) 
  ON Vendors.AddressStateID = States.ID
 WHERE 
  Closers.ID = @VendorID

 DECLARE @Abbr1 VARCHAR(MAX)
 SET @Abbr1= @StatesCovered + ', '
 
 SELECT @Abbr1 = @Abbr1 + Abbr + ', ' FROM
 (SELECT DISTINCT Abbr
 FROM VendorCounties WITH (NOLOCK)
 JOIN States WITH (NOLOCK) ON VendorCounties.StateID = States.ID
 WHERE VendorID = @VendorID AND Abbr != @StatesCovered) AS x

 RETURN LEFT(@Abbr1,LEN(@Abbr1)-1)

 END

GO

Getting the UPS shipping label via their API

After integrating our site with FedEx, we decided to also offer support for UPS. Some of our clients use FedEx and some use UPS. The FedEx integration was fairly quick and easy. The UPS integration is basically the same so far.

To get started, UPS provides a document and code sample for creating a shipment via the UPS Shipping Web Service API, but the code does not show how to get the actual shipping label.

Hopefully the following code helps someone else. For example, UPS returns the label in two parts when you request a GIF image of the label. The response gives you the GIF image and an HTML page. The HTML page references the image. Specifically, the page looks for a file named labelTRACKINGNUMBER.gif.

Here's the code. Enjoy!

     protected void Button1_Click(object sender, EventArgs e)  
     {  
       try  
       {  
         ShipService shipService = new ShipService();  
         ShipmentRequest shipmentRequest = new ShipmentRequest();  
   
         shipService.UPSSecurityValue = GetUpsShipSecurityValue();  
   
         UpsShipService.RequestType request = new UpsShipService.RequestType();  
         String[] requestOption = { "nonvalidate" };  
         request.RequestOption = requestOption;  
         shipmentRequest.Request = request;  
   
         UpsShipService.ShipmentType shipment = new UpsShipService.ShipmentType();  
         shipment.Description = "This is the description...";  
   
         shipment.PaymentInformation = GetUpsPaymentInfo();  
         shipment.Shipper = GetUpsShipper();  
         shipment.ShipFrom = GetUpsShipFrom();  
         shipment.ShipTo = GetUpsShipTo();  
         shipment.Service = GetUpsShipmentService();  
         shipment.Package = GetUpsPackageType();  
         shipmentRequest.LabelSpecification = GetUpsLabelSpecification();  
   
         shipmentRequest.Shipment = shipment;  
   
         ShipmentResponse shipmentReponse =
              shipService.ProcessShipment(shipmentRequest);  
   
         string graphicImage =
              shipmentReponse.ShipmentResults.PackageResults[0].ShippingLabel.GraphicImage;  
   
         byte[] byteLabel1 = Convert.FromBase64String(graphicImage);  
         string shippingLabelFileName1 = SaveShippingLabelGIF(byteLabel1, "_labels\\", shipmentReponse.ShipmentResults.ShipmentIdentificationNumber);  
   
         string htmlImage =
              shipmentReponse.ShipmentResults.PackageResults[0].ShippingLabel.HTMLImage;  
   
         byte[] htmlLabel = Convert.FromBase64String(htmlImage);  
         string shippingLabelFileNameHtml1 = 
              SaveShippingLabelHTML(htmlLabel, "_labels\\",shipmentReponse.ShipmentResults.ShipmentIdentificationNumber);    
   
         Label1.Text = string.Format("{0}: Tracking: {1}; Cost: {2}", 
              shipmentReponse.Response.ResponseStatus.Description, 
              shipmentReponse.ShipmentResults.ShipmentIdentificationNumber,
              shipmentReponse.ShipmentResults.ShipmentCharges.TotalCharges.MonetaryValue);  
   
         System.Uri uri = new System.Uri(Request.Url.AbsoluteUri);  

         HyperLink1.Text = shippingLabelFileNameHtml1;
         HyperLink1.Target = "_blank";
         HyperLink1.NavigateUrl = string.Format("http://{0}/_labels/{1}", uri.Host, shippingLabelFileNameHtml1);  
       }  
       catch (System.Web.Services.Protocols.SoapException exSoap)  
       {  
         Label1.Text = string.Format("Soap Exception: {0}", exSoap.Detail.InnerText);  
       }  
       catch (Exception ex)  
       {  
         Label1.Text = string.Format("Exception: {0}", ex.Message);  
       }  
   
     }  
   
     private UpsShipService.UPSSecurity GetUpsShipSecurityValue()  
     {  
       UpsShipService.UPSSecurity upsSecurity = new UpsShipService.UPSSecurity();  
   
       UpsShipService.UPSSecurityServiceAccessToken upsSecurityAccessToken = 
              new UpsShipService.UPSSecurityServiceAccessToken();  
       upsSecurityAccessToken.AccessLicenseNumber = "";  
       upsSecurity.ServiceAccessToken = upsSecurityAccessToken;  
   
       UpsShipService.UPSSecurityUsernameToken upsSecurityUserNameToken = 
              new UpsShipService.UPSSecurityUsernameToken();  
       upsSecurityUserNameToken.Username = "YOUR API USERNAME";  
       upsSecurityUserNameToken.Password = "YOUR API PASSWORD";  
       upsSecurity.UsernameToken = upsSecurityUserNameToken;  
   
       return upsSecurity;  
     }  
   
     private PaymentInfoType GetUpsPaymentInfo()  
     {  
       PaymentInfoType paymentInfo = new PaymentInfoType();  
       ShipmentChargeType shipmentCharge = new ShipmentChargeType();  
       BillShipperType billShipper = new BillShipperType();  
       billShipper.AccountNumber = "YOUR ACCOUNT NUMBER";  
       shipmentCharge.BillShipper = billShipper;  
       shipmentCharge.Type = "01"; // credit card type...required?  
   
       ShipmentChargeType[] shipmentChargeArray = { shipmentCharge };  
       paymentInfo.ShipmentCharge = shipmentChargeArray;  
   
       return paymentInfo;  
     }  
   
     private ShipperType GetUpsShipper()  
     {  
       ShipperType shipper = new ShipperType();  
       shipper.ShipperNumber = "YOUR ACCOUNT NUMBER";  
   
       ShipAddressType shipperAddress = new ShipAddressType();  
   
       String[] addressLine = { "3136 Kingsdale Center, #117" };  
       shipperAddress.AddressLine = addressLine;  
       shipperAddress.City = "Upper Arlington";  
       shipperAddress.PostalCode = "43221";  
       shipperAddress.StateProvinceCode = "OH";  
       shipperAddress.CountryCode = "US";  
         
       shipper.Address = shipperAddress;  
       shipper.Name = "Signature Closers";  
       shipper.AttentionName = "*** Attention Line ***";  
   
       ShipPhoneType shipperPhone = new ShipPhoneType();  
       shipperPhone.Number = "8886777462";  
       shipper.Phone = shipperPhone;  
   
       return shipper;  
     }  
   
     private ShipFromType GetUpsShipFrom()  
     {  
       ShipFromType shipFrom = new ShipFromType();  
       ShipAddressType shipFromAddress = new ShipAddressType();  
   
       String[] addressLine = { "3136 Kingsdale Center, #117" };  
       shipFromAddress.AddressLine = addressLine;  
       shipFromAddress.City = "Upper Arlington";  
       shipFromAddress.PostalCode = "43221";  
       shipFromAddress.StateProvinceCode = "OH";  
       shipFromAddress.CountryCode = "US";  
       shipFrom.Address = shipFromAddress;  
   
       shipFrom.Name = "Signature Closers";  
       shipFrom.AttentionName = "*** FROM Attention Line ***";  
   
       return shipFrom;  
     }  
   
     private ShipToType GetUpsShipTo()  
     {  
       ShipToType shipTo = new ShipToType();  
       ShipToAddressType shipToAddress = new ShipToAddressType();  
   
       String[] addressLine1 = { "5168 Birchwood Farms Dr." };  
       shipToAddress.AddressLine = addressLine1;  
       shipToAddress.City = "Mason";  
       shipToAddress.PostalCode = "45040";  
       shipToAddress.StateProvinceCode = "OH";  
       shipToAddress.CountryCode = "US";  
       shipTo.Address = shipToAddress;  
       shipTo.AttentionName = "Schap!";  
       shipTo.Name = "Schapplication";  
   
       ShipPhoneType shipToPhone = new ShipPhoneType();  
       shipToPhone.Number = "5135555555";  
       shipTo.Phone = shipToPhone;  
   
       return shipTo;  
     }  
   
     private UpsShipService.ServiceType GetUpsShipmentService()  
     {  
       //01 = Next Day Air  
       //02 = 2nd Day Air  
       //03 = Ground  
       //07 = Express  
       //08 = Expedited  
       //11 = UPS Standard  
       //12 = 3 Day Select  
       //13 = Next Day Air Saver  
       //14 = Next Day Air Early AM  
       //54 = Express Plus  
       //59 = 2nd Day Air A.M.  
       //65 = UPS Saver  
       //82 = UPS Today Standard  
       //83 = UPS Today Dedicated Courier  
       //84 = UPS Today Intercity  
       //85 = UPS Today Express  
       //86 = UPS Today Express Saver  
       //96 = UPS Worldwide Express Freight  
   
       UpsShipService.ServiceType service = 
              new UpsShipService.ServiceType();  
       service.Code = "01";  
       return service;  
     }  
   
     private UpsShipService.PackageType[] GetUpsPackageType()  
     {  
       UpsShipService.PackageType package = new UpsShipService.PackageType();  
   
       PackageWeightType packageWeight = new PackageWeightType();  
       packageWeight.Weight = "1";  
   
       ShipUnitOfMeasurementType uom = new ShipUnitOfMeasurementType();  
       uom.Code = "LBS";  
       packageWeight.UnitOfMeasurement = uom;  
       package.PackageWeight = packageWeight;  
   
       //01 = UPS Letter  
       //02 = Customer Supplied Package  
       //03 = Tube  
       //04 = PAK  
       //21 = UPS Express Bo   
       //24 = UPS 25KG Box  
       //25 = UPS 10KG Box  
       //30 = Pallet  
       //2a = Small Express Box  
       //2b = Medium Express Box   
       //2c = Large Express Box   
   
       PackagingType packType = new PackagingType();  
       packType.Code = "01";  
       package.Packaging = packType;  
       UpsShipService.PackageType[] pkgArray = { package };  
   
       return pkgArray;  
     }  
   
     private LabelSpecificationType GetUpsLabelSpecification()  
     {  
       LabelSpecificationType labelSpec = new LabelSpecificationType();
   
       LabelImageFormatType labelImageFormat = new LabelImageFormatType();  
       labelImageFormat.Code = "GIF";  
       labelSpec.LabelImageFormat = labelImageFormat;  
   
       return labelSpec;  
     }  
   
     /// <summary>  
     /// Saves the GIF part of the label as labelTRACKINGNUMBER.gif.  
     /// </summary>  
     /// <returns>The saved file name.</returns>  
     private string SaveShippingLabelGIF(byte[] labelBuffer, string folder, string trackingNumber)  
     {  
       string fileName = string.Format("label{0}.gif", trackingNumber);  
       return SaveShippingLabel(labelBuffer, folder, trackingNumber, fileName);  
     }  
   
     /// <summary>  
     /// Sets the HTML part of the label as "page_TRACKINGNUMBER.html" and saves the label.  
     /// </summary>  
     /// <returns>The saved file name.</returns>  
     private string SaveShippingLabelHTML(byte[] labelBuffer, string folder, string trackingNumber)  
     {  
       string fileName = string.Format("page_{0}.html", trackingNumber);  
       return SaveShippingLabel(labelBuffer, folder, trackingNumber, fileName);  
     }  
   
     private string SaveShippingLabel(byte[] labelBuffer, string folder, string trackingNumber, string fileName)  
     {  
       string returnFileName = fileName;  
   
       string phyPath = HttpRuntime.AppDomainAppPath;  
       string fullPath = String.Format("{0}{1}", phyPath, folder);  
       if (!System.IO.Directory.Exists(fullPath)) System.IO.Directory.CreateDirectory(fullPath);  
       string pathToCheck = String.Format("{0}{1}", fullPath, returnFileName);  
   
       // Should just delete the old file (code copied from another project)  
       if (System.IO.File.Exists(pathToCheck))  
       {  
         int counter = 1;  
         while (System.IO.File.Exists(pathToCheck))  
         {  
           returnFileName = counter.ToString() + fileName;  
           pathToCheck = String.Format("{0}{1}", fullPath, returnFileName);  
           counter++;  
         }  
       }  
   
       System.IO.FileStream LabelFile = new System.IO.FileStream(pathToCheck, System.IO.FileMode.Create);  
       LabelFile.Write(labelBuffer, 0, labelBuffer.Length);  
       LabelFile.Close();  
   
       return returnFileName;  
     }