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

Using a cursor for this is probably the slowest possible solution. If performance is important then there are three valid approaches. The first approach is FOR XML without special XML character protection.

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');

SELECT 
  t.id, 
  city = STUFF((
    SELECT ',' + t2.city
    FROM @t t2
    WHERE t.id = t2.id
    FOR XML PATH('')),1,1,'')
FROM @t as t
GROUP BY t.id;

The drawback to this approach is when you add a reserved XML character such as &, <, or >, you will get an XML entity back (e.g. "&amp" for "&"). To handle that you have to modify your query to look like this:

Sample data

IF OBJECT_ID('tempdb..#t') IS NOT NULL DROP TABLE #t;
CREATE TABLE #t (id int, words varchar(20))
INSERT #t VALUES (1, 'blah blah'),(1, 'yada yada'),(2, 'PB&J'),(2,' is good');

SELECT 
  t.id, 
  city = STUFF((
    SELECT ',' + t2.words
    FROM #t t2
    WHERE t.id = t2.id
    FOR XML PATH(''), TYPE).value('.','varchar(1000)'),1,1,'')
FROM #t as t
GROUP BY t.id;

The downside to this approach is that it will be slower. The good news (and another reason this approach is 100 times better than a cursor) is that both of these queries benefit greatly when the optimizer chooses a parallel execution plan.

The best approach is a new fabulous function available in SQL Server 2017, STRING_AGG. STRING_AGG does not have the problem with special XML characters and is, by far the cleanest approach:

SELECT t.id, STRING_AGG(t.words,',') WITHIN GROUP (ORDER BY t.id)
FROM #t as t
GROUP BY t.id;

Answer 2

I have written nested cursor to sync with distinct city id. Although it has performance issue, you can try the following procedure

CREATE PROCEDURE USP_CITY
AS
BEGIN
    set nocount on
    declare @mastertid int
    declare @detailstid int
    declare @tcity varchar(MAX)
    declare @finalCity varchar(MAX) 
    SET  @finalCity = ''


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

    declare @finaltable table (id int, city varchar(max))

    declare MasterCityCursor CURSOR FOR


        select distinct id from @t 
        order by id

       open MasterCityCursor
       fetch next from MasterCityCursor into @mastertid

       while ( @@FETCH_STATUS = 0)
       begin
            declare DetailsCityCursor CURSOR FOR
            SELECT id,city from @t order by id

            open DetailsCityCursor
            fetch next from DetailsCityCursor into @detailstid,@tcity

            while ( @@FETCH_STATUS = 0)
            begin
                if @mastertid = @detailstid
                begin
                    SET @finalCity = @finalCity + CASE @finalCity WHEN '' THEN +'' ELSE ', ' END + @tcity
                end
                fetch next from DetailsCityCursor into @detailstid, @tcity
            end

            insert into @finaltable values(@mastertid,@finalCity)
            SET @finalCity = ''
            close DetailsCityCursor
            deallocate DetailsCityCursor
            fetch next from MasterCityCursor into @mastertid
       end

    close MasterCityCursor
    deallocate MasterCityCursor

    SELECT * FROM @finaltable
 END  

If you will face any problem, feel free to write in comment section. Thanks

Answer 3

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.