连接/转换基于客户 # 的多行

Question

我目前有一份报告，其中包含下周将由我们执行工作的客户列表。

目前，当报告运行时，我们会生成客户列表、要执行的工作日期、工作类型及其电子邮件地址。

其中许多客户端在该周内执行了多项服务，因此在报告中多次出现。

我想将列表中多次出现的任何客户端组合成这样：

---

我们目前拥有的：

Cust ID  FName LName       Date      Description
   1     Jon   Smith     01/01/17   Spring Cleanup
   1     Jon   Smith     01/03/17   Lawn Maintenance
   1     Jon   Smith     01/05/17   Irrigation
   2     Jane  Roberts   01/02/17   Spring Cleanup
   2     Jane  Roberts   01/03/17   Lawn Maintenance
   3     Jim   Whoever   01/04/17   Turf

---

我们想要什么：

Cust ID  FName Lname       Date       Description
   1     Jon   Smith     01/01/17    Spring Cleanup, Lawn Maintenance, Irrigation
   2     Jane  Roberts   01/02/17    Spring Cleanup, Lawn Maintenance
   3     Jim   Whoever   01/04/17    Turf

以下是到目前为止我们对代码的内容：

Select 
    cust.CustID,
    cust.CustName, 
    cust.FirstName, 
    cust.LastName, 
    cust.Email, 
    wo.ShortDesc,
    wos.StartTime, 
    br.Description Branch
From 
    WorkOrderSchedules wos Join
    WorkOrders wo On wo.SvcOrderID = wos.SvcOrderID Join
    Customers cust On cust.CustID = wo.CustID Join
    Branches br On br.LocationID = wo.LocationID
Where 
  wos.StartTime Between DateAdd(wk, 2, DateAdd(wk, DateDiff(wk, 7,
  GetDate()), -1)) And DateAdd(wk, 2, DateAdd(wk, DateDiff(wk, 7, GetDate()),
  5)) And cust.CustName Not Like 'Three C%' And wo.ShortDesc Not Like
  'sales lead' And wo.ShortDesc Not Like '%lawn main%' And
  cust.CustName Not Like 'Port' And cust.CustName Not Like '31 Mile%' And
  cust.CustName Not Like '32 Mile' And cust.CustName Not Like 'Quail Ridge' And
  cust.CustName Not Like 'Hayes' And cust.CustName Not Like 'Inla' And
  cust.CustName Not Like 'Eaton' And cust.CustName Not Like 'Fisher' And
  cust.CustName Not Like 'Pasadena' And cust.CustName Not Like 'Mallard'
Group By
    cust.CustID,
    cust.CustName, 
    cust.FirstName, 
    cust.LastName, 
    cust.Email, 
    wo.ShortDesc,
    wos.StartTime, 
    br.Description
Order By 
    cust.CustName, 
    wos.StartTime

提前感谢您的任何帮助，因为我对 SQL 非常陌生，并感谢任何帮助。

Answer 1

这是

模拟GROUP CONCAT。

Select 
    cust.CustID,
    cust.CustName, 
    cust.FirstName, 
    cust.LastName, 
    cust.Email, 
    wo.ShortDesc,
    Min(wos.StartTime) as StartTime, 
    --br.Description Branch
    Branch = STUFF((
                  SELECT ',' + md.Description
                  FROM dbo.Branches md
                  WHERE br.LocationID = md.LocationID
                  FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
From 
    WorkOrderSchedules wos Join
    WorkOrders wo On wo.SvcOrderID = wos.SvcOrderID Join
    Customers cust On cust.CustID = wo.CustID Join
    Branches br On br.LocationID = wo.LocationID
Where 
  wos.StartTime Between DateAdd(wk, 2, DateAdd(wk, DateDiff(wk, 7,
  GetDate()), -1)) And DateAdd(wk, 2, DateAdd(wk, DateDiff(wk, 7, GetDate()),
  5)) And cust.CustName Not Like 'Three C%' And wo.ShortDesc Not Like
  'sales lead' And wo.ShortDesc Not Like '%lawn main%' And
  cust.CustName Not Like 'Port' And cust.CustName Not Like '31 Mile%' And
  cust.CustName Not Like '32 Mile' And cust.CustName Not Like 'Quail Ridge' And
  cust.CustName Not Like 'Hayes' And cust.CustName Not Like 'Inla' And
  cust.CustName Not Like 'Eaton' And cust.CustName Not Like 'Fisher' And
  cust.CustName Not Like 'Pasadena' And cust.CustName Not Like 'Mallard'
group by
    cust.CustID,
    cust.CustName, 
    cust.FirstName, 
    cust.LastName, 
    cust.Email, 
    wo.ShortDesc,