title: SQL Server : combine multiple rows
body: <p>I have an <code>EMPLOYEE</code> table that references the roles assigned to an employee, and the skills the employee has. I am trying create a SELECT statement using SQL Server that will return, per row, the Employee #, Employee Login, Employee Name, Facility, Work Center, Roles and Skills. The roles are referenced in another table as are the skills.</p>
  <p>My issue is that I cannot seem to write a SQL query such that an employee with multiple roles and/or skills would have each skill listed per employee record.</p>
  <p>My current SQL only retrieves one role/skill per row.</p>
  <pre><code>SELECT 
      e.EmployeeNo, e.Name, e.LoginName, f.Facility, w.WorkCenter, 
      r.Role AS Roles, s.Name as Skills
  FROM
      EMPLOYEE e
  LEFT JOIN 
      EMPLOYEE_FACILITY f ON e.ID = f.EmployeeID
  LEFT JOIN 
      EMPLOYEE_WORK_CENTER w ON e.ID = w.EmployeeID
  JOIN 
      EMPLOYEE_ROLE er ON e.ID = er.EmployeeID
  LEFT JOIN 
      ROLE r ON er.ID = r.ID
  JOIN 
      EMPLOYEE_SKILL es ON e.ID = es.EmployeeID
  LEFT JOIN 
      SKILL s ON es.ID = s.ID
  GROUP BY
      e.EmployeeNo, e.Name, e.LoginName, f.Facility, w.WorkCenter, 
      r.Role, s.Name
  ORDER BY
      e.EmployeeNo
  </code></pre>
  <p>The output I'm looking for is:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>Employee #</th>
  <th>Employee Name</th>
  <th>loginName</th>
  <th>Facility</th>
  <th>WorkCenter</th>
  <th>Roles</th>
  <th>Skills</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>123456789</td>
  <td>Test</td>
  <td>TestLogin</td>
  <td>ABC1</td>
  <td>MACHINING</td>
  <td>IT</td>
  <td>CPP</td>
  </tr>
  <tr>
  <td></td>
  <td></td>
  <td></td>
  <td></td>
  <td></td>
  <td>ADMIN</td>
  <td>SCRUM</td>
  </tr>
  <tr>
  <td>234567890</td>
  <td>TestUser</td>
  <td>Test2</td>
  <td>DEF2</td>
  <td>STAMPING</td>
  <td>FAB</td>
  <td>LABOR</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p><a href="https://i.stack.imgur.com/4mQf0.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/4mQf0.png" alt="Current Output" /></a>
  As you can see, I'm getting multiple rows for each Employee #, I'm trying to combine all the roles and skills for a given employee into a single row.</p>
  <p>EDIT:
  Is it possible to combine the rows with JOINs? I am rather rusty in SQL, and the last time I used MySQL was years ago.</p>