title: How to use the result of a stuff-function in an IN-Clause?
body: <p>I want to use the result of a concatenated string in an IN-Clause.</p>
  <p>This is my SQL-Query:</p>
  <pre><code>UPDATE Customer
  SET Agent = 'Test'
  WHERE CAST(ID AS VARCHAR) IN (
          SELECT IIF((
                      SELECT WinnerID
                      FROM Customer
                      WHERE ID = '19560'
                      ) IS NULL, '19560', (
                      SELECT STUFF((
                                  SELECT ',' + CAST(ID AS VARCHAR)
                                  FROM Customer
                                  WHERE WinnerID = '19560'
                                  FOR XML PATH('')
                                  ), 1, 1, '') --returns 19560, 19686
                      ))
          )
  </code></pre>
  <p>The part in the IIF functions where the expression returns false is the problem. The correct values are returned, but they are not recognized correctly, so the table is not updated.</p>
  <p><strong>This is the table structure with some sample date</strong></p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>ID</th>
  <th>WinnerID</th>
  <th>CustomerName</th>
  <th>Agent</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>19560</td>
  <td>19560</td>
  <td>Carman Sansone</td>
  <td>NULL</td>
  </tr>
  <tr>
  <td>19686</td>
  <td>19560</td>
  <td>Melania Snowden</td>
  <td>NULL</td>
  </tr>
  <tr>
  <td>19404</td>
  <td>NULL</td>
  <td>Esther Flansburg</td>
  <td>NULL</td>
  </tr>
  <tr>
  <td>19405</td>
  <td>NULL</td>
  <td>Maximo Schill</td>
  <td>NULL</td>
  </tr>
  <tr>
  <td>20055</td>
  <td>20055</td>
  <td>Jeanice Moen</td>
  <td>NULL</td>
  </tr>
  <tr>
  <td>20056</td>
  <td>20055</td>
  <td>Kraig Rochin</td>
  <td>NULL</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>The WinnerID is the result of a duplicate resolution. Thus, two customers who have the same WinnerID are the same customer and should be assigned the same agent.</p>
  <p>Is there any way to formulate this part of the query so that I can use an IN-clause? Or to reformulate the whole query to achieve the desired outcome?</p>