组合SELECT子查询的和并连接具有空值的行

Question

我正在运行一个查询，该查询使用group by查询从数据库中提取发票信息，以汇总四个不同的行项目，并按月对它们进行分组。该查询涉及JOIN中的3个表：CustomerAccount、Invoice和InvoiceDetail。当我执行查询时，我得到了正确的信息，但是它被分成四行，每一列有一个值，而其余的都是NULL。我如何组合这些行，使它们都显示在一行上？

下面是我的问题：

SELECT
    [Client].[CustID] AS [Account#],
    [Client].[Branch] AS [Branch],
    [Client].[Dept] AS [Department],
    CONCAT(DATEPART(YEAR, [dbo].[Invoice].[Date]), '-', RIGHT('00' + CONVERT(NVARCHAR(2), DATEPART(MONTH, [dbo].[Invoice].[Date])), 2) ) AS [Date],
    [Client].[Name],
    (SELECT FORMAT(SUM([InvDetail].[SubTotal]), 'C', 'en-us') WHERE [InvDetail].[Description] = 'Delivery/Pickup') AS [Dlvry],
    (SELECT FORMAT(SUM([InvDetail].[SubTotal]), 'C', 'en-us') WHERE [InvDetail].[Description] = 'File Storage') AS [Storage],
    (SELECT FORMAT(SUM([InvDetail].[SubTotal]), 'C', 'en-us') WHERE [InvDetail].[Description] = 'BOX RETRIEVAL' OR [InvDetail].[Description] = 'BOX RETFILE') AS [Ret/Ref],
    (SELECT FORMAT(SUM([InvDetail].[SubTotal]), 'C', 'en-us') WHERE [InvDetail].[Description] LIKE 'Shredding%') AS [Shredding],
    FORMAT(SUM([InvDetail].[SubTotal]), 'C', 'en-us') AS [SubTotal],
    FORMAT(SUM([InvDetail].[GST]), 'C', 'en-us') AS [GST],
    FORMAT(SUM([InvDetail].[PST]), 'C', 'en-us') AS [PST],
    FORMAT(SUM([InvDetail].[Total]), 'C', 'en-us') AS [Total]
FROM [dbo].[CustomerAccount] AS [Client] LEFT JOIN [dbo].[Invoice]
    ON [Client].[CustID] = [dbo].[Invoice].[CustID]
LEFT JOIN [dbo].[InvoiceDetail] AS [InvDetail]
    ON [dbo].[Invoice].[InvoiceNumber] = [InvDetail].[InvoiceNumber]

WHERE DATEPART(YEAR, [dbo].[Invoice].[Date]) >= 2015
      AND DATEPART(YEAR, [dbo].[Invoice].[Date]) <= 2017
      AND [Acct] = @ACCOUNT#
      AND [Branch] = @BRANCH
      AND [Dept] = @DEPARTMENT
GROUP BY [Client].[CustID], 
         [Client].[Branch], 
         [Client].[Dept], 
         [Client].[Name],
         DATEPART(YEAR, [dbo].[Invoice].[Date]),
         DATEPART(MONTH, [dbo].[Invoice].[Date]),
         [InvDetail].[Description]

ORDER BY [Client].[CustID]

查询结果如下所示：

| Account# | Branch | Department | Date    | Name | Delvry | Storage | Ret/Ref | Shredding
| 1000     | MAIN   | TAX        | 2016-01 | Acme | $50.00 | NULL    | NULL    | NULL
| 1000     | MAIN   | TAX        | 2016-01 | Acme | NULL   | $200.00 | NULL    | NULL
| 1000     | MAIN   | TAX        | 2016-01 | Acme | NULL   | NULL    | $124.00 | NULL
| 1000     | MAIN   | TAX        | 2016-01 | Acme | NULL   | NULL    | NULL    | $12.75

我希望它看起来像这样：

| Account# | Branch | Department | Date    | Name | Delvry | Storage | Ret/Ref | Shredding
| 1000     | MAIN   | TAX        | 2016-01 | Acme | $50.00 | $200.00 | $124.00 | $12.75

我不知所措，不知如何继续下去。

Answer 1

考虑到您所获得的查询结果，您将存储在一个名为IntermediateTable的表中。获取所需结果的查询将为：

SELECT Account# 
       ,Branch
       ,Department
       ,Date
       ,Name
       ,SUM(ISNULL(Delvry,0))
       ,SUM(ISNULL(Storage,0))
       ,SUM(ISNULL(Ret/Ref,0))
       ,SUM(ISNULL(Shredding,0))
FROM IntermediateTable
GROUP BY Account# 
       ,Branch
       ,Department
       ,Date
       ,Name