This post is in continuation to my previous post on “Converting Row Data as Comma Separated String”, which is to demonstrate exactly the opposite process “Converting a Comma Separated String Having IDs to Row Result”.
Example Requirement Case:
There is a table by name tbl_CityList (as in the snapshot below) with two columns, RecID which is an identity column and CityIDList which is VarChar(100) holding the RecID separated by comma from the table tbl_CityState (as explained in my previous post).
The task is to convert the list of City IDs separated by comma into row result (as in the snapshot below)
Solution:
User Defined Function (UDF): The UDF func_CityByRow
CREATE FUNCTION dbo.func_CityByRow
(
@CityIDList VarChar(5000)
)
RETURNS @RtnValue TABLE
(
RecID INT identity(1,1),
StateName VarChar(50),
CityName VarChar(50)
)
AS
BEGIN
DECLARE @SplitOn as VarChar(10)
SET @SplitOn = ','
WHILE (Charindex(@SplitOn,@CityIDList)>0)
BEGIN
INSERT INTO
@RtnValue (StateName, CityName)
Select top 1 StateName,CityName from tbl_CityState
Where RecID=ltrim(rtrim(Substring(@CityIDList,1,Charindex(@SplitOn,@CityIDList)-1)))
SET @CityIDList = Substring(@CityIDList,Charindex(@SplitOn,@CityIDList)+len(@SplitOn),len(@CityIDList))
END
INSERT INTO @RtnValue (StateName, CityName)
Select top 1 StateName,CityName from tbl_CityState
Where RecID=ltrim(rtrim(@CityIDList))
RETURN
END
The function above takes the CityIDList as input parameter and returns table. The CityIDList is read item by item by eliminating comma and a new record is inserted in the return variable of table type.
DECLARE @cityIDList as varchar(500)
Select @cityIDList=CityIDList from tbl_CityList where RecID=1
SELECT a.StateName, a.CityName FROM dbo.func_CityByRow(@cityIDList) as a
Run the above code to get results as shown below.