如何调整SQL查询，使结果按Works Order Number每行仅返回一条数据

你的问题根源在于GROUP BY子句包含了ProductKitManufacturedProcessID和AVOOptionTotalCostInCurrency，导致每个不同的工序ID会单独生成一行。要实现每个工单编号一行数据，需要用聚合函数把对应类型的成本汇总，同时调整分组逻辑：

DECLARE @PreviousStart SmallDateTime
DECLARE @PreviousEnd SmallDateTime
SET @PreviousStart = '11-01-2023 00:00:00'
SET @PreviousEnd = '11-30-2023 00:00:00'

Select WorksOrderNumber [Works Order Number],
SUM(Case When ProductKitManufacturedProcessID = 3 THEN AVOOptionTotalCostInCurrency ELSE 0 END) as [Truss Lumber],
SUM(Case When ProductKitManufacturedProcessID = 4 THEN AVOOptionTotalCostInCurrency ELSE 0 END) as [Truss Plates],
SUM(Case When ProductKitManufacturedProcessID = 5 THEN AVOOptionTotalCostInCurrency ELSE 0 END) as [Truss Labor],
SUM(Case When ProductKitManufacturedProcessID = 6 THEN AVOOptionTotalCostInCurrency ELSE 0 END) as [Truss Overhead]
from WorksOrderheader WOH
LEFT OUTER JOIN WorksOrderLine WOL on WOH.WorksOrderID = WOL.WorksOrderID AND WOL.ProductKitManufacturedProcessID is not null
Where BranchID = 1 AND
WorksOrderNumber != 0 AND
DateTimeCreated between @PreviousStart and @PreviousEnd AND
Description like '%Floor%'
Group by WorksOrderNumber
Order By WorksOrderNumber

关键修改说明： #

• 给每个CASE WHEN语句套上SUM()聚合函数，同一工单下对应工序的成本值会自动累加（如果同一工单同一工序有多条记录，也能完成汇总）
• GROUP BY仅保留WorksOrderNumber，确保每个工单编号只生成一行结果
• 在CASE WHEN里添加ELSE 0，避免未匹配到工序时出现NULL值，保证数值字段的完整性

内容的提问来源于stack exchange，提问作者Purpleman101