Concatenate Row Data in Column

Existing Table:- Surrounding_City

Id City SurroundingCity
67 Madurai Madurai
68 Madurai Dindigul
69 Madurai Palani
70 Madurai Kodaikanal
71 Madurai Sivakasi
72 Madurai Virudhunagar
73 Madurai Rajapalayam
74 Madurai Theni


Step 1:: Create Function
Create Function CityConcatenate (@City Varchar(100)) returns varchar(2000)
as
begin
Declare @var varchar(2000)
Set @var='';
Select @var=@var+'

'+surroundingcity+'

' from surrounding_city Where city=@City
return @var
end

Step 1:: Call Function in Query
Select distinct city,dbo.CityConcatenate(city) from surrounding_city

Looping in Cities to insert

Alter proc TestCity_Assigned --'bihar'
(@state varchar(100))
as begin
declare @mycity varchar(100)

declare cursor1 cursor for
select distinct city from area (nolock) where state=@state
open cursor1
fetch next from cursor1 into @mycity

while @@fetch_status=0
begin
if not exists(select * from city_assigned (nolock) where name='Bikash' and assigned_city=@mycity)
insert into city_assigned(name,assigned_city,status) values('Bikash',@mycity,'Super Manager')
fetch next from cursor1 into @mycity
end
close cursor1
deallocate cursor1
end

TestCity_Assigned 'bihar'

Looping In Sql Server

DECLARE @Flag INT
SET @Flag = 1
WHILE (@Flag < 10)
BEGIN
BEGIN
PRINT @Flag
SET @Flag = @Flag + 1
END
IF(@Flag > 5)
BREAK
ELSE
CONTINUE
END

Function to Concatenate Strings In SQL

Alter function dbo.ConcatAreaName(@pincode varchar(20))
RETURNS varchar(2000)
As
Begin
Declare @var varchar(2000)
set @var=''
Select @var = @var +'#'+ areaname from area where pincode=@pincode group by areaname
return @var
End

To Get the Result from Function :
Concatenate Areaname on the basis of pincode :
select distinct pincode, dbo.ConcatAreaName(pincode) from area where pincode='124505'
select distinct top 10 pincode, dbo.ConcatAreaName(pincode) from area

Capturing IP Address and System Name

Response.Write("Host name : "+ System.Net.Dns.GetHostName().ToString());

Response.Write("Host address : "+System.Net.Dns.GetHostAddresses(System.Net.Dns.GetHostName()).GetValue(0).ToString());