Monday, March 18, 2013

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