SQL Server equivalent to GROUP_CONCAT()

Question

I have this database:

And I need to get the following data for each Client:

• Client Name
• Contract Name(s)
• Project(s)
• Employees who logged hours to a project from the first day of the current month until the last day of the current month
• Total number of hours logged for each employee during the month
• Employee rate
• Total Charges per employee (i.e. employee rate x employee hours worked)
• Billing contact(s) [name, address] for each contract

I have the following query so far, but I need to implement a MSSQL version of MySQL's GROUP_CONCAT( )

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

When I started following this example, I got to here and stopped because I realized that I couldn't reference table aliases (Cr) from other subqueries (ProjectNames).

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

How exactly do I go about doing this?

Answer 1

Try CROSS APPLY to do yhis:

For Example:

SELECT Cl.LegalName AS ClientNames,
B.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 ) 
CROSS APPLY (
    SELECT Cr1.ContractDesc + ', '
    FROM Contracts Cr1
    WHERE Cl.ClientID = Cr1.ClientID 
    FOR XML PATH('')
) B (ContractDesc)
WHERE WH_Month = 4 AND WH_Year = 2013