相当于GROUP_CONCAT()的SQL Server [英] SQL Server equivalent to GROUP_CONCAT()

问题描述

我有这个数据库:

并且我需要为每个客户端获取以下数据:

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

• 客户名称
• 合同名称
• 项目
• 从当月第一天到当月最后一天登录项目时间的员工
• 该月每个员工记录的总小时数
• 员工率
• 每位员工的总费用(即员工费率x员工工作时间)
• 每份合同的结算联系人[姓名，地址]

到目前为止，我有以下查询，但是我需要实现MySQL的GROUP_CONCAT()的MSSQL版本

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

当我开始关注此时例如，我到这里停了下来，因为我意识到我无法从其他子查询(ProjectNames)中引用表别名(Cr).

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

我到底该怎么做?

推荐答案

做到这一点

   SELECT Cl.LegalName AS ClientNames,
   ContractNames
   FROM Clients Cl
    cross apply 
   (SELECT Cr1.ContractDesc + ', '
    FROM Contracts Cr1
      WHERE Cl.ClientID = Cr1.ClientID  For XML PATH(''))a1 (ContractNames)

这篇关于相当于GROUP_CONCAT()的SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！