Concat multiple rows into a comma-delimited value during Update

Question

I have a temporary table with a field called Method, thus:

DECLARE @CaseSites TABLE (
 BriefID  int,
 Method  varchar(60)
 -- other fields
)

Method will be filled from several rows in another table - CaseEventTypeList.

Running

SELECT * FROM CaseEventTypeList WHERE RefID = 1

Gives

RefID TypeID
1  2
1  3
1  6

Turning this into a single comma delimited result is fairly trivial:

DECLARE @CETList varchar(30)

SELECT @CETList = COALESCE(@CETList + ',', '') + CAST(CETL.[TypeID] AS varchar)
FROM CaseEventTypeList CETL 
WHERE CETL.RefID = 1

PRINT @CETList

Giving:

2,3,6

Now I need to expand this to take in the entire table. This is what I came up with:

UPDATE @CaseSites SET Method = COALESCE(Method + ',','') + CAST(CETL.TypeID AS VARCHAR)
 FROM CaseEvents CE
   JOIN CaseEventTypeList AS CETL ON CETL.RefID = CE.TypeListID
 WHERE BriefID = CE.CaseID

However this only fills Method with the first value from each set of values.

I looked online and found this but would rather not use a udf - especially when the solution feels so close.

UPDATE: The data is fairly simple, the RefId is incremented for each case, the TypeID can be any number, though only 1 to 8 are modelled currently. Thus you might have:

RefID TypeID
12  2
12  7
13  1
14  1
14  3
14  6

And this will hopefully be modelled as

SELECT Method from @CaseSites
Method 
...
12  2,7
13  1
14  1,3,6
...

Answer 1

I think your problem is because the update statment only evaluates the "SET Method = " once per row, hence you only get one value in the list.

A UDF would be the easy way to do this, but since you are using temporary tables this may not be an option and you wished to avoid them anyway. So you may need to use a cursor (not nice) but gets the job done the way you want.

Here's what I came up with based on your original sql.

DECLARE myCURSOR Cursor
FOR
  Select BriefID
  from #CaseSites

Open myCursor
DECLARE @BriefID int
DECLARE @CETList varchar(30)
Fetch NEXT FROM myCursor INTO @BriefID
While (@@FETCH_STATUS <> -1)
BEGIN
  IF (@@FETCH_STATUS <> -2)

  SET @CETList = ''
  SELECT @CETList = COALESCE(@CETList + ',', '') + CAST(CETL.[TypeID] AS varchar)
    FROM #CaseEventTypeList CETL 
   WHERE CETL.RefID = @BriefID 

  UPDATE #CaseSites
     SET Method = @CETList
   WHERE BriefID = @BriefID

  Fetch NEXT FROM myCursor INTO @BriefID
END
CLOSE myCursor
DEALLOCATE myCursor

Answer 2

I have found a better answer than my first if you are ok with using xml: A correlated subquery using xml.

UPDATE #CaseSites 
   SET Method = (   
                  select  cast([TypeID] as varchar(30))+ ',' 
                    from #CaseEventTypeList
                   where RefID = CE.CaseID 
                     for xml path ('') 
                )
  FROM #CaseEvents CE