Cross Apply In SQL Server

I will be introducing you with magic feature from Microsoft SQL-Server 2005, Called CROSS APPLY. If you want to get all Geo information where city are separated by “,” you can use udf or sub query for CROSS APPLY.

Create a Geo table.

Geo
City	State
Rajkot, Ahmedabad, Vadodara, Surat, Jamnagar	Gujarat
Mumbai, Pune, Nagpur, Nasik	Maharashtra
Bangalore, Mysor, Mangalore	Karnataka

Expected Result

To retrieve all Geo information with separated city.

City	State	OrgCity
Ahmedabad	Gujarat	Rajkot, Ahmedabad, Vadodara, Surat, Jamnagar
Jamnagar	Gujarat	Rajkot, Ahmedabad, Vadodara, Surat, Jamnagar
Rajkot	Gujarat	Rajkot, Ahmedabad, Vadodara, Surat, Jamnagar
Surat	Gujarat	Rajkot, Ahmedabad, Vadodara, Surat, Jamnagar
Vadodara	Gujarat	Rajkot, Ahmedabad, Vadodara, Surat, Jamnagar
Bangalore	Karnataka	Bangalore, Mysor, Mangalore
Mangalore	Karnataka	Bangalore, Mysor, Mangalore
Mysor	Karnataka	Bangalore, Mysor, Mangalore
Mumbai	Maharashtra	Mumbai, Pune, Nagpur, Nasik
Nagpur	Maharashtra	Mumbai, Pune, Nagpur, Nasik
Nasik<	Maharashtra	Mumbai, Pune, Nagpur, Nasik
Pune	Maharashtra	Mumbai, Pune, Nagpur, Nasik

(1) Create a function that accept string value and return table, after splitting with special character.

Create Function[dbo].[fnSplit]
(
@sInputList VARCHAR(200)
)
RETURNS @List TABLE (item VARCHAR (8000))
BEGIN

DECLARE @sDelimiter varchar(2)
SET @sDelimiter =',' --Set Delimiter value

DECLARE @sItem VARCHAR(8000)

WHILE CHARINDEX(@sDelimiter, @sInputList, 0) <> 0
BEGIN

SELECT @sItem = RTRIM(LTRIM(SUBSTRING(@sInputList, 1, CHARINDEX(@sDelimiter, @sInputList, 0)-1))), @sInputList=RTRIM(LTRIM (SUBSTRING(@sInputList, CHARINDEX(@sDelimiter, @sInputList, 0)+LEN(@sDelimiter),LEN(@sInputList))))

IF LEN(@sItem) > 0

INSERT INTO @List SELECT @sItem

END

IF LEN(@sInputList) > 0

INSERT INTO @List SELECT @sInputList -- Put the last item in

RETURN

END

How to execute above function

Select * from dbo.fnSplit('Rajkot, Ahmedabad, Vadodara, Surat, Jamnagar')
orderby item

Result:
Ahmedabad
Jamnagar
Rajkot
Surat
Vadodara

(2) To retrieve above Expected Result use following query

SELECT Item as City, [State], City as OrgCity
FROM Geo
CROSS APPLY [dbo].[fnSplit](Geo.city)
Orderby [State], Item

==============================================
Nirman Doshi|Senior Software Engineer

WebMingle Technology
Accelerated by knowledge. Driven by values.
www.webMingle.in