Create delimited field from data rows

• Nigel Ellis

  Grasshopper

  Points: 12

  More actions

  May 27, 2004 at 6:51 am

  #160952

  I currently have a table with records like

  cityid, area_desc

  -----------------

  JKF, NEW YORK

  LGA, NEW YORK

  NYC, NEW YORK

  LNS, LANCASTER

  ZRL, LANCASTER

  How can I get a result like:

  JFK|LGA|NYC, NEW YORK

  LNS,ZRL, LANCASTER

  * two fields, one with the codes delimited by a '|' and the other with the names.

  Thanks

  Nigel Ellis

• Nigel Ellis

  Grasshopper

  Points: 12

  More actions

  May 27, 2004 at 9:05 am

  #508034

  After trawling the Internet, and then asking a few people, I managed to find a solution. Here it is. First, create a stored procedure

  CREATE PROCEDURE sp_concat_city_airpt @destination varchar(50), @airports varchar(100) output AS

  SELECT @airports = COALESCE(@airports + '|', '') + area_cityid

  FROM city_airpt c

  where area_desc in (select distinct area_desc from city_airpt where area_desc = @destination) and area_hide = 0

  Then I used the following statement:

  declare @alist varchar(100)

  declare @areadesc varchar(100)

  DECLARE cur CURSOR FOR

  select distinct area_desc from city_airpt where area_hide = 0

  OPEN cur

  FETCH NEXT FROM cur into @areadesc

  while @@fetch_status = 0

  begin

   set @alist = null

   exec sp_concat_city_airpt @areadesc, @alist output

   select distinct @alist as airports, area_desc as destination from city_airpt ca where area_desc = @areadesc

   FETCH NEXT FROM cur into @areadesc

  end

  close cur

  deallocate cur

  I am not entirely sure how this work, but it does.