如果您希望列按照您的描述进行结构化，实现这一目标的一种方法可能是使用 PIVOT 和动态 SQL。

PIVOT 函数允许您将行转换为列，而动态 SQL 提供了动态生成列名（例如，您示例中的月份的开始）的灵活性。特别是如果查询旨在聚合未知时间段内的数据。

这种方法还使数据能够驱动输出。这样您指定的日期越多，查询应按预期输出。

CREATE TABLE #CustomerTable (Customer VARCHAR(20),[Date] DATETIME,Quantity INT)
INSERT INTO #CustomerTable VALUES
('cname-AA','2021/03/01',10),('cname-AA','2021/03/02',20),'2021/04/01','2021/04/02',('cname-BB',15),25),35)

-- stores dynamically generated months from dataset and the query to execute
DECLARE @Months AS NVARCHAR(MAX) = ''
DECLARE @Query  AS NVARCHAR(MAX) = ''

-- setup the months to PIVOT on
SELECT @Months = @Months + QUOTENAME(MonthStart) + ',' 
FROM (SELECT DISTINCT CONVERT(VARCHAR(7),ct.[Date],126)  AS MonthStart
      FROM #CustomerTable ct) as tmp
SELECT @Months = SUBSTRING(@Months,LEN(@Months))

SET @Query = '
SELECT *
FROM (
    SELECT ct.Customer,CONVERT(VARCHAR(7),126) AS MonthStart,ct.Quantity
    FROM #CustomerTable ct
    ) AS Src
    PIVOT (
        SUM(Quantity)
        FOR MonthStart IN (' + @Months + ')
    )   AS PivotTable';

EXECUTE(@Query)

DROP TABLE #CustomerTable

输出：

如果您只有日期，而没有时间，如示例中所示。此查询应该有效：

SELECT CUSTOMER,SUM(CASE WHEN DATE BETWEEN '2021-03-01 00:00:00' AND '2021-03-31 00:00:00' THEN QUANTITY ELSE 0 END) MAR21,SUM(CASE WHEN DATE BETWEEN '2021-04-01 00:00:00' AND '2021-04-30 00:00:00' THEN QUANTITY ELSE 0 END) APR21
FROM TABLE1
GROUP BY CUSTOMER

这是小提琴： http://sqlfiddle.com/#!18/0da73c/4

要使用此功能，您的案例中需要更多列，每个月 10 列。

如果我理解正确，您可以使用条件聚合：

select customer,sum(case when month(date) = 3 then quantity end) as mar,sum(case when month(date) = 4 then quantity end) as apr
from t
group by customer;

不清楚是否需要 where 子句将其限制在特定时间段内，例如：

where date >= '2021-03-01' and date < '2021-05-01'

我知道这与您想要的输出不匹配，但我想向您展示一个更动态的替代方案，这意味着您不必在查询中对月份和年份进行“硬编码”。

WITH CTE AS
(
SELECT Customer,DATEPART(YEAR,DateTime2Field) As MyYear,DATEPART(Month,DateTime2Field) AS MyMonth,Quantity
FROM MyTable
)

SELECT Customer,CONCAT(MyYear,'-',MyMonth) AS [Year-Month],SUM(Quantity) AS SummedQuantiy
FROM CTE
GROUP BY Customer,MyYear,MyMonth
ORDER BY Customer,MyMonth