代码之家 › 专栏 › 技术社区 › Bmw

多行对一行结果

tsql sql-server-2005 sql

1

Bmw · 技术社区 · 16 年前

我希望我能解释清楚。我在苦苦思索这个问题:

InvoiceNum
Amount
Type  - where type could be item, shipping or tax.

所以我想要返回的是每张发票一行:InvoiceNum,itemamunit,ShippingAmount,TaxAmount。

Invoicenum Amount Type
1          $32    Item
1          $2     Shipping
1          $1     Tax

我想退货:

InvoiceNum ItemAmount ShippingAmount TaxAmount 
1          $32        $2             $1

2 回复 | 直到 16 年前

1

4

Andomar 16 年前

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

2

3

Bill Karwin 16 年前

正如@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;