How to sum column with pivoting other column in T-SQL-CodePudding

How do you sum up the 'Fee' column and make unique ControlNo in the below example?

Code sample:

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

Output looks like this:

But needs to look like this:

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

UPDATE:

Following Dale's solution

I mimic real data as much as possible, but for some reason 81 of TerrPrem is disappearing?

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

Why one of the TerrPrem is dissappearing?

                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

CodePudding user response：

Just another option... Expand the rows via a CROSS APPLY and add [Fee] to the 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

Results

OR... You can use a conditional aggregation

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

CodePudding user response：

For only 2 line types, you would be better off using a case expression

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;