title: SQL Server equivalent to GROUP_CONCAT()
body: <p>I have this database: </p>
  
  <p><img src="https://i.stack.imgur.com/hQWN8.png" alt="enter image description here"></p>
  
  <p>And I need to get the following data for <strong>each</strong> Client:</p>
  
  <ul>
  <li>Client Name</li>
  <li>Contract Name(s)</li>
  <li>Project(s)</li>
  <li>Employees who logged hours to a project from the first day of the current month until the last day of the current month</li>
  <li>Total number of hours logged for each employee during the month</li>
  <li>Employee rate </li>
  <li>Total Charges per employee (i.e. employee rate x employee hours worked)</li>
  <li>Billing contact(s) [name, address] for each contract</li>
  </ul>
  
  <p>I have the following query so far, but I need to implement a MSSQL version of MySQL's GROUP_CONCAT( )</p>
  
  <pre><code>SELECT 
  
  Cl.LegalName AS ClientNames,
  Cr.ContractDesc AS ContractNames,
  P.ProjectName AS ProjectNames,
  ( E.FirstName + ' ' + E.LastName ) AS EmployeeNames,
  SUM( WH.HoursWorked ) AS TotalHours, 
  ( SUM( WH.HoursWorked ) * BR.Rate ) AS TotalCharges, 
  ( Ca.FirstName + Ca.LastName + ', ' + Ca.AddrLine1 ) AS BillingContacts
  
  FROM Clients Cl
  JOIN Contracts Cr
  ON( Cl.ClientID = Cr.ClientID )
  
  JOIN Projects P 
  ON( Cr.ContractID = P.ContractID )
  
  JOIN EmployeesProjects EP
  ON( P.ProjectID = EP.ProjectID )
  
  JOIN Employees E 
  ON( EP.EmpID = E.EmpID )
  
  JOIN WorkHours WH
  ON( E.EmpID = WH.EmpID )
  
  JOIN BillingRates BR
  ON( E.TitleID = BR.TitleID ) AND ( E.Level = BR.Level )
  
  JOIN ContractsContacts CC
  ON( Cr.ContractID = CC.ContractID )
  JOIN Contacts Ca
  ON( CC.ContactID = Ca.ContactID )
  
  WHERE WH_Month = 4
  AND WH_Year = 2013
  </code></pre>
  
  <p>When I started following <a href="https://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005">this</a> example, I got to here and stopped because I realized that I couldn't reference table aliases (Cr) from other subqueries (ProjectNames).</p>
  
  <pre><code>SELECT 
  Cl.LegalName AS ClientNames, 
  (
      SELECT ContractDesc + ', '
      FROM Contracts Cr
      WHERE Cl.ClientID = Cr.ClientID 
      FOR XML PATH('')
  ) ContractNames,
  (
      SELECT ProjectName + ', '
      FROM Projects P
      WHERE Cr.ContractID = P.ContractID
      FOR XML PATH('')
  ) ProjectNames
  
  
  FROM Clients Cl
  </code></pre>
  
  <p>How exactly do I go about doing this?</p>