多行对一行结果

group by ,您可以使用 case :

select  InvoiceNum
,       sum(case when Type = 'Item' then Amount end) as ItemAmount
,       sum(case when Type = 'Shipping' then Amount end) as ShippingAmount
,       sum(case when Type = 'Tax' then Amount end) as TaxAmount
from    YourTable
group by
        InvoiceNum

case语句返回 null 默认情况下,以及 sum

正如@Andomar所展示的那样,你可以使用分组和求和技巧(max也可以)。

或者,Microsoft SQL Server支持 PIVOT 在这种类型的查询中有一定帮助的操作。不过,您仍然需要硬编码列名。

SELECT InvoiceNum, [Item] AS ItemAmount, [Shipping] AS ShippingAmount, [Tax] AS TaxAmount
FROM 
(SELECT InvoiceNum, Amount, Type FROM InvoiceTable ) i
PIVOT
(
  MAX(Amount)
  FOR Type IN ([Item], [Shipping], [Tax])
) AS pvt;