Concate string into temp table in cursor

I have a temp table structure @temp2 like this

route   total    driverID
------------------------
10B      300    
7B       400

and would like to add the driverID column with a result like 5555, 68989

to do this, I use the cursor to loop another table look like this

driverID    routeNo
-------------------
5555        10B
68989       10B
72000       7B

Here is the code

declare @driverID varchar(max)
declare @routeNew varchar(20)



DECLARE brand_cursor CURSOR FOR     

        select distinct driver_id, route_number from [dbcwl].[dbo].[collection_deduction_summary] 
        where YEAR(trans_date) =  @year
        and MONTH(trans_date) = @month
        and route_number in (select actual_route from [dbcwl].[dbo].[livedispatchingdata_pmhs_daily_summary] where status = 'OK' and YEAR(trans_date) = @year AND month(trans_date) = @month )
        and vehicle_id in (select vehicle_id from [dbcwl].[dbo].[livedispatchingdata_pmhs_daily_summary] where status = 'OK' and YEAR(trans_date) = @year AND month(trans_date) = @month  )
        group by route_number, driver_id
        OPEN brand_cursor    

FETCH NEXT FROM brand_cursor     
INTO @driverID, @routeNew

WHILE @@FETCH_STATUS = 0    
BEGIN    
        --update @temp2
        update @temp2 set driverID +=  ','+ @driverID where routeNo = @routeNew; 

        FETCH NEXT FROM brand_cursor     
    INTO @driverID, @routeNew

    END     
    CLOSE brand_cursor;    
    DEALLOCATE brand_cursor;

sadly, I got the driverID column is null

and would like to have finalized temp table look like this:

 route   total    driverID
    ------------------------
    10B      300    5555,68989
    7B       400    72000

Answer

;with cte as -- Step 1: Get all Driver Ids by routeNo
(
  SELECT routeNo, driverID = 
    STUFF((SELECT DISTINCT ', ' + CAST(driverID AS NVARCHAR(100))
           FROM #A b 
           WHERE b.routeNo = a.routeNo 
          FOR XML PATH('')), 1, 2, '')
FROM #A a
GROUP BY routeNo
)
update b -- Step 2: Update driverID accordingly.
set driverID = cte.driverID
from #B b
inner join cte on b.route = cte.routeNo

Result here

Advertisement

Answer