Concatenating with Cursor

Question

I really want to learn and understand how to concatenate strings with the cursor approach.

Here is my table:

declare @t table (id int, city varchar(15))
insert into @t values 
    (1, 'Rome')
    ,(1, 'Dallas')
    ,(2, 'Berlin')
    ,(2, 'Rome')
    ,(2, 'Tokyo')
    ,(3, 'Miami')
    ,(3, 'Bergen')

I am trying to create a table that has all cities for each ID within one line sorted alphabetically.

ID  City
1   Dallas, Rome
2   Berlin, Rome, Tokyo
3   Bergen, Miami

This is my code so far but it is not working and if somebody could walk me through each step I would be very happy and eager to learn it!

set nocount on
declare @tid int
declare @tcity varchar(15)



declare CityCursor CURSOR FOR
    select * from @t 
    order by id, city

   open CityCursor

   fetch next from CityCursor into @tid, @tcity

   while ( @@FETCH_STATUS = 0)
   begin

        if @tid = @tid -- my idea add all cities in one line within each id
            print cast(@tid as varchar(2)) + ', '+ @tcity 
        else if @tid <> @tid --when it reaches a new id and we went through all cities it starts over for the next line
        fetch next from CityCursor into @tid, @tcity
   end

   close CityCursor
   deallocate CityCursor

   select * from CityCursor

Answer 1

First, for future readers: A cursor, as Sean Lange wrote in his comment, is the wrong tool for this job. The correct way to do it is using a subquery with for xml.

However, since you wanted to know how to do it with a cursor, you where actually pretty close. Here is a working example:

set nocount on
declare @prevId int, 
        @tid int,
        @tcity varchar(15)

declare @cursorResult table (id int, city varchar(32)) 
-- if you are expecting more than two cities for the same id, 
-- the city column should be longer

declare CityCursor CURSOR FOR
select * from @t 
order by id, city

open CityCursor

fetch next from CityCursor into @tid, @tcity

while ( @@FETCH_STATUS = 0)
begin

    if @prevId is null or @prevId != @tid 
        insert into @cursorResult(id, city) values (@tid, @tcity)
    else 
        update @cursorResult
        set city = city +', '+ @tcity
        where id = @tid

    set @prevId = @tid    
    fetch next from CityCursor into @tid, @tcity
end

close CityCursor
deallocate CityCursor

select * from @cursorResult

results:

id  city
1   Dallas, Rome
2   Berlin, Rome, Tokyo
3   Bergen, Miami

I've used another variable to keep the previous id value, and also inserted the results of the cursor into a table variable.