Using COALESCE to combine zipcodes in a state table

[goofy78270]

I am trying to list each distinct state and county with a comma deliminated field for all zipcodes within that county and state. I have the following code, but it seems to combine all zipcodes within the table and not the ones related to the state and county.

DECLARE @ZipCode varchar(1000)

Select @ZipCode = COALESCE(@ZipCode + ', ', '') +
CAST(zip AS varchar(5))
FROM empower.ZIPCODES

Select distinct State, County, @ZipCode
From empower.ZIPCODES
order by state, county

 

[ESquared]

Unfortunately, there is no concatenation aggregate function. If there was, it'd be a really simple query.

Honestly, this sounds like a client display issue. The best thing to do is select the State, County, and Zipcode columns as a full rowset and do the concatenation in the client. If you're concerned about the size of the rowset, you can use state & county artificial keys that are much smaller than the full length text of each one's name. With proper sorting, simulating a merge join in the client is quite easy and saves a lot of bandwidth. If you cache certain lookup data in the application, you can also save a lot of database hits and network traffic.

Other than handling it in the client, you're basically stuck with a loop that starts with a temp table having each State & County, and one at a time collecting the zip codes for the row into a variable and updating the row. Best practice is to avoid this at all costs.

If you have SQL 2005 you CAN do a CROSS APPLY function to get the job done, but you'll need a custom function that accepts a State & County as input and returns a comma-delimited list of zip codes for just that. This is really no different in effect and cost than the custom looping method, it's just easier to code.

I have this idea for a super-powerful and -flexible object that can transform rowsets in all sorts of ways (including pivoting) and spit out a new rowset, or array, or excel file, or csv, or whatever. That object would be perfect for this in the client.

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

_{The best part about anything that has cheese is the cheese.}[/color]

 

[SQLDenis]

This has been demonstrated many times by George the Great
You need a UDF which accepts county and zipcode
then you would join the table with that function and voila

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[goofy78270]

Can you explain the loop process a little more?

 

[gmmastros]

Here: thread183-1159740

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

Just for fun, I wrote a function for this.

It takes a long time to run (about a minute). I noticed something interesting that I wanted to give you a heads up on. Los Angeles County, California has the most number of zip codes (542). Assuming you are using 5 digit zip codes, that is 5 * 542 for the data + 541 for the commas. This amounts to 3,251 characters. In your sample code, you are using a varchar(1000), which isn't enough.

Since ZipCode data is relatively static, I encourage you to create a real table and store this data. Then, when you need it, simply select from the table.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[goofy78270]

I changed the size of zipcodes. Thank You

I am trying to do this as an export as I do not have table rights to create a new table.

I am simply trying to create a download for this. This is a once a year type thing and a new table is something the DBAs do not want.

 

[SQLDenis]

>>I am trying to do this as an export as I do not have table rights to create a new table.


but you can create a function? AFAIK remember

CREATE FUNCTION permissions default to members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles. Members of sysadmin and db_owner can grant CREATE FUNCTION permissions to other logins by using the GRANT statement.

so if you have that role you can also create a table

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[ESquared]

Some of what I said wasn't right. Where I mentioned SQL 2005, that functionality is in 2000: the UDF that everyone's talking about.

Cross apply would only be necessary if the UDF was returning more than one row.

 

[goofy78270]

I am not sure what you mean by creating a function. I am trying to run this set of SQL commands within a Excel macro which I believe accomplishes this through a T-SQL type interface. I understand I can do this manipulation in Excel with a macro, but I am interested in learning how to do this within SQL.

NOTE: I am using SQL 2005 on a 2003 server.

The data I am pulling is a HUD imported table that contains every state, county, city, and zipcode for the US. I am trying to pull in every distinct sate and county with a consolidated field for the zip codes.

 

[ESquared]

CREATE FUNCTION ZipList(
   @State varchar(100),
   @County varchar(100)
)
RETURNS varchar(8000)
AS
BEGIN
   DECLARE @List varchar(8000)
   SELECT @List = Coalesce(@List + ',', '') + ZipCode
   FROM ZipCodes
   WHERE
      State = @State
      AND County = @County
   RETURN @List
END

SELECT
   State,
   County,
   dbo.ZipList(State, County)
FROM County
WHERE State = 'Euphoria'

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

_{The best part about anything that has cheese is the cheese.}[/color]