CREATE TABLE STATEZIP (
STATE varchar(28),
ZIP char(5)
)
INSERT STATEZIP (STATE, ZIP)
SELECT 'New York', '11229'
UNION ALL 
SELECT 'New York', '11230'
UNION ALL
SELECT 'New Jersey', '08401' 
UNION ALL
SELECT 'California', '93765' 
UNION ALL
SELECT 'California','94706' 
UNION ALL
SELECT 'New York','11235' 

GO

CREATE FUNCTION dbo.udf_GetZipByState(@statename varchar(28))
RETURNS VARCHAR(1000) AS

BEGIN
   DECLARE @ziplist varchar(1000)

   SELECT @zipList = COALESCE(@zipList + ', ', '') + s.zip
   FROM Statezip s
   WHERE s.state = @statename

   RETURN @ziplist
END

GO

SELECT distinct state as StateName, dbo.udf_GetZipByState(state) as 'Zip Codes'
FROM statezip s