Thursday, July 30, 2009

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

    No comments: