Databases
StackOverflow2013
Postsdbo
POConcat multiple rows into a comma-delimited value during Update
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POConcat multiple rows into a comma-delimited value during Update
    text
    copied!<p>I have a temporary table with a field called Method, thus:</p> <pre><code>DECLARE @CaseSites TABLE ( BriefID int, Method varchar(60) -- other fields ) </code></pre> <p>Method will be filled from several rows in another table - CaseEventTypeList.</p> <p>Running</p> <pre><code>SELECT * FROM CaseEventTypeList WHERE RefID = 1 </code></pre> <p>Gives</p> <pre><code>RefID TypeID 1 2 1 3 1 6 </code></pre> <p>Turning this into a single comma delimited result is fairly trivial:</p> <pre><code>DECLARE @CETList varchar(30) SELECT @CETList = COALESCE(@CETList + ',', '') + CAST(CETL.[TypeID] AS varchar) FROM CaseEventTypeList CETL WHERE CETL.RefID = 1 PRINT @CETList </code></pre> <p>Giving:</p> <pre><code>2,3,6 </code></pre> <p>Now I need to expand this to take in the entire table. This is what I came up with:</p> <pre><code>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 </code></pre> <p>However this only fills Method with the first value from each set of values.</p> <p>I <a href="http://www.sqlprof.com/blogs/sqldev/archive/2008/03/31/how-to-list-multiple-rows-of-data-on-same-line.aspx" rel="nofollow noreferrer">looked online and found this</a> but would rather not use a udf - especially when the solution feels so close.</p> <p><strong>UPDATE:</strong> 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:</p> <pre><code>RefID TypeID 12 2 12 7 13 1 14 1 14 3 14 6 </code></pre> <p>And this will hopefully be modelled as</p> <pre><code>SELECT Method from @CaseSites Method ... 12 2,7 13 1 14 1,3,6 ... </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload