title: Get rows from different table in a column in select query
body: <p>I want to be able to see the rows from different table in a single column .</p>
  <p>Just like this question <a href="https://stackoverflow.com/questions/69717300/how-to-select-row-s-from-an-other-table-using-a-comma-separated-value-from-the-f">How to select row/s from an other table using a comma-separated value from the first table?</a> (which is a duplicate of this question: <a href="https://stackoverflow.com/questions/69717300/how-to-select-row-s-from-an-other-table-using-a-comma-separated-value-from-the-f">MySQL Join two tables with comma separated values :</a>) but for Sql Server.</p>
  <p>Tables from link:</p>
  <p>Table 1. <code>faculty</code></p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>subject</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>101, 102</td>
  </tr>
  <tr>
  <td>104</td>
  </tr>
  <tr>
  <td>103, 105</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Table 2. <code>subject</code></p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>code</th>
  <th>subject</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>101</td>
  <td>subject 1</td>
  </tr>
  <tr>
  <td>102</td>
  <td>subject 2</td>
  </tr>
  <tr>
  <td>103</td>
  <td>subject 3</td>
  </tr>
  <tr>
  <td>104</td>
  <td>subject 4</td>
  </tr>
  <tr>
  <td>105</td>
  <td>subject 5</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Expected Output:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>subject</th>
  <th>subject</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>101, 102</td>
  <td>subject 1, subject 2</td>
  </tr>
  <tr>
  <td>104</td>
  <td>subject 4</td>
  </tr>
  <tr>
  <td>103, 105</td>
  <td>subject 3, subject 5</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Basically I don't want to see multiple columns for joins but all corresponding values in on column comma separated.</p>