Just a couple of suggestions, folks...

First, there is no need for a loop in the creation of a "Tally" or "Numbers" table. Second, to be truly effective and to eliminate the need for embedded TOP clauses and Order By's, it must have a Clustered Primary Key on the "N" column (like Adam did). With that in mind, here's the quickest way to make a permanent Tally table in SS2K5...

-===== Create and populate the Tally table on the fly (2k5 version)

SELECT TOP 11000 --More than 30 years worth of days if converted to dates

N = IDENTITY(INT,1,1)

INTO dbo.Tally

FROM Master.sys.All_Columns ac1

CROSS JOIN Master.sys.All_Columns ac2

--===== Add the necessary Clustered PK for blinding speed

ALTER TABLE dbo.Tally

ADD CONSTRAINT PK_Tally_N PRIMARY KEY CLUSTERED (N)

--===== Allow everyone to use the table

GRANT SELECT ON dbo.Tally TO PUBLIC

See? No explicit loop and runs like an ape with it's hair on fire. Of course, if you want your Tally table to contain more numbers, just change the number after the TOP clause.

You also don't need explicit loops to solve the problem. In fact, you don't even need a UDF and, contrary to what everyone has said, you can still solve the problem in a single step...

--=======================================================================================

-- Create and populate a test table... THIS IS NOT PART OF THE SOLUTION

--=======================================================================================

CREATE TABLE #AgentStatus

(

AgentID INT,

AgentType INT,

CityID VARCHAR(1000)

)

INSERT INTO #AgentStatus

(AgentID,AgentType,CityID)

SELECT 10,3,'1/2/5/12/20' UNION ALL

SELECT 20,1,'2/4/6/8/10' UNION ALL

SELECT 30,2,'1/3/5/7/9/11/35' UNION ALL

SELECT 40,4,'5' UNION ALL

SELECT 50,9,'20/30'

--=======================================================================================

-- Solve the problem in a "single step" using the Tally table

--=======================================================================================

--===== Declare a variable to hold the delimiter

DECLARE @Delim CHAR(1)

SET @Delim = '/'

--===== Do the split and required concatenation all at once

SELECT CAST(AgentID AS VARCHAR(10))+'-'

+ CAST(AgentType AS VARCHAR(10))+'-'

+ SUBSTRING(@Delim+h.CityID+@Delim, t.N+1, CHARINDEX(@Delim, @Delim+h.CityID+@Delim, t.N+1)-t.N-1)

FROM dbo.Tally t

RIGHT OUTER JOIN --Necessary in case CityID is NULL

dbo.#AgentStatus h

ON SUBSTRING(@Delim+h.CityID+@Delim, t.N, 1) = @Delim

AND t.N < LEN(@Delim+h.CityID+@Delim)

And, look ma, still no explicit loop... no XML required, either