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.
SELECT * FROM CaseEventTypeList WHERE RefID = 1
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
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 ...