如何在T-SQL中使用透視其他列對列求和-有解無憂

在下面的示例中，您如何總結“費用”列并制作唯一的 ControlNo？

代碼示例：

IF OBJECT_ID('tempdb..#table1') IS NOT NULL 
    DROP TABLE #table1

CREATE TABLE #Table1 
(
     ControlNo INT, 
     Line varchar(50), 
     Profit INT, 
     Fee INT
)

INSERT INTO #Table1 (ControlNo, Line, Profit, Fee) 
VALUES (1111, 'Line1', 80, 30), 
       (1111, 'Line2', 100, 20), 
       (3333, 'Line1', 200, 50), 
       (4444, 'Line1', 50, 10), 
       (4444, 'Line2', 100, 40)

-- check
--select * from #Table1

SELECT * 
FROM #Table1
PIVOT
    (SUM(Profit)
        FOR Line IN ([Line1], [Line2])
    ) pvt
ORDER BY ControlNo

輸出如下所示：

如何在 T-SQL 中使用透視其他列對列求和

但需要看起來像這樣：

ControlNo   Fee Line1   Line2
1111        50  80      100
3333        50  200     0
4444        50  50      100

更新：

遵循戴爾的解決方案

我盡可能地模仿真實資料，但由于某種原因，TerrPrem 的 81 正在消失？

IF OBJECT_ID('tempdb..#table1') IS NOT NULL DROP TABLE #table1

Create table #Table1 ( Guid uniqueidentifier, ControlNo int, Line varchar(50), Prem INT, TerrPrem int )

INSERT INTO #Table1 (Guid, ControlNo, Line, Prem, TerrPrem) 
    VALUES  ('169E54D8-F00A-43B8-9268-5DD3F5684C5A',4395768, 'Commercial General Liability',10987,0), 
            ('169E54D8-F00A-43B8-9268-5DD3F5684C5A',4395768, 'Commercial General Liability',81,81), 
            ('169E54D8-F00A-43B8-9268-5DD3F5684C5A',4395768, 'Contractors Pollution Liability',1013,0), 
            ('169E54D8-F00A-43B8-9268-5DD3F5684C5A',4395768, 'Contractors Pollution Liability',81,81)

-- check
--select * from #Table1



select * 
from #Table1
PIVOT(
SUM(Prem)
FOR Line IN ([Commercial General Liability],             
             [Contractors Pollution Liability])
             ) as PivotTable

為什么 TerrPrem 之一正在消失？

                Guid                    ControlNo   TerrPrem    Commercial General Liability    Contractors Pollution Liability
169E54D8-F00A-43B8-9268-5DD3F5684C5A    4395768         0                   10987                           1013
169E54D8-F00A-43B8-9268-5DD3F5684C5A    4395768         81                    81                            81

uj5u.com熱心網友回復：

只是另一種選擇...通過 a 展開行CROSS APPLY并添加[Fee]到PIVOT.

Select ControlNo
      ,Fee   = IsNull(Fee,0)
      ,Line1 = IsNull(Line1,0)
      ,Line2 = IsNull(Line2,0)
 From (
        select ControlNo 
              ,B.*
         From #Table1 A
         Cross Apply ( values ('Fee',Fee)
                             ,(Line,Profit)
                     ) B(Item,Value)
      ) src
 Pivot ( sum(Value) for Item in ([Fee],[Line1],[Line2] ) ) pvt

結果

如何在 T-SQL 中使用透視其他列對列求和

或...您可以使用條件聚合

Select ControlNo
      ,Fee   = sum(Fee)
      ,Line1 = sum( case when Line='Line1' then Profit else 0 end)
      ,Line2 = sum( case when Line='Line2' then Profit else 0 end)
 From  #Table1
 Group By ControlNo

uj5u.com熱心網友回復：

對于只有 2 種線型，最好使用case運算式

select [Guid], ControlNo, TerrPrem * count(*)
  , sum(case when Line = 'Commercial General Liability' then Prem else 0 end) [Commercial General Liability]
  , sum(case when Line = 'Contractors Pollution Liability' then Prem else 0 end) [Contractors Pollution Liability]
from #table1
group by [Guid], ControlNo, TerrPrem
order by [Guid], ControlNo, TerrPrem;

轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/311543.html

標籤：sql sql-server 查询语句枢

上一篇：SQL在多個條目中查找不存在資源的位置

下一篇：用Pythonic方法比較兩個字典，并在鍵值匹配的地方追加嵌套物件。