CODEWITHSUNDEEP
sqlsql-server
Carlos80
Carlos80

Reputation: 433

SQL Pivot Table with SUM

I'm trying to amend the following SQL code into a pivot table. The original data looks like so:

PerilCode       B               C           BI
EQ          179166451986    27296144046   9067728654
WS          182394050346    28745459712   9148728654
SL          114374574342    12703142574   293860386
TC          182394050346    28745459712   9148728654
WF          182394050346    28745459712   9148728654
FF          182394050346    28745459712   9148728654
ST          182394050346    28745459712   9148728654

The code is below:

SELECT
PL.PerilCode,
SUM(ReplacementValueA) AS 'B',
SUM(ReplacementValueC) AS 'C',
SUM(ReplacementValueD) AS 'BI'

FROM [SE-SQLTO-0300].[AIRExposure_London].[dbo].[tLocation] L

INNER JOIN [SE-SQLTO-0300].[AIRExposure_London].[dbo].[tExposureSet] ES ON L.ExposureSetSID = ES.ExposureSetSID
INNER JOIN [SE-SQLTO-0300].[AIRProject].[dbo].[tExposureViewDefinition] EVD ON ES.ExposureSetSID = EVD.ExposureSetSID
INNER JOIN [SE-SQLTO-0300].[AIRProject].[dbo].[tExposureView] EV ON EVD.ExposureViewSID = EV.ExposureViewSID
INNER JOIN [SE-SQLTO-0300].[AIRProject].[dbo].[tProjectExposureViewXref] PEV ON EV.ExposureViewSID = EV.ExposureViewSID
INNER JOIN [SE-SQLTO-0300].[AIRProject].[dbo].[tProject] P ON PEV.ProjectSID = P.ProjectSID
INNER JOIN [SE-SQLTO-0300].[AIRExposure_London].[dbo].[tLocTerm] LT ON L.LocationSID = LT.LocationSID
INNER JOIN [SE-SQLTO-0300].[AIRReference].[dbo].[tPerilSetXref] PSX ON LT.PerilSetCode = PSX.PerilSetCode
INNER JOIN [SE-SQLTO-0300].[AIRReference].[dbo].[tPeril] PL ON PSX.PerilCode = PL.PerilCode

WHERE P.ProjectName = 'Pricing' AND EV.ExposureViewName = 'CAP Maxed'

GROUP BY PL.PerilCode

Ideally what I'm trying to get the pivot to look like is like so:

     EQ      WS      SL      TC      WF      FF      ST 
 B   179,166,451,986     182,394,050,346     114,374,574,342     182,394,050,346     182,394,050,346     182,394,050,346     182,394,050,346 
 C   27,296,144,046      28,745,459,712      12,703,142,574      28,745,459,712      28,745,459,712      28,745,459,712      28,745,459,712 
 BI      9,067,728,654   9,148,728,654   293,860,386     9,148,728,654   9,148,728,654   9,148,728,654   9,148,728,654

Upvotes: 0

Views: 1415

Answers (3)

mohan111
mohan111

Reputation: 8865

using cross apply also we can achieve 

declare @t table (Perilcode varchar(2),B BIGINT,C BIGINT,BI BIGINT)
insert into @t(Perilcode,B,C,BI)values ('EQ',179166451986,27296144046,9067728654),
('WS',182394050346,28745459712,9148728654),('SL',114374574342,12703142574,293860386),
('TC',182394050346,28745459712,9148728654),('WF',182394050346,28745459712,9148728654),('FF',182394050346,28745459712,9148728654),
('ST',182394050346,28745459712,9148728654)
;with CTE AS(
select col,col1,col2 from @t CROSS APPLY 
                    (Select Perilcode As Col,B As Col1,'B' as Col2 UNION ALL 
                    SELECT Perilcode As Col, 
                    C As Col1,'C' as Col2  UNION ALL 
                    SELECT Perilcode As Col, BI As Col1,'BI' as Col2  )A(col,Col1,col2)
                    GROUP BY col,Col1,col2)


select P.col2 As Perilcode,REPLACE(CONVERT(VARCHAR,CAST([EQ]AS MONEY),1),'.00','')[EQ],
REPLACE(CONVERT(VARCHAR,CAST([WS]AS MONEY),1),'.00','')[WS],
REPLACE(CONVERT(VARCHAR,CAST([SL]AS MONEY),1),'.00','')[SL],
REPLACE(CONVERT(VARCHAR,CAST([TC]AS MONEY),1),'.00','')[TC],
REPLACE(CONVERT(VARCHAR,CAST([WF]AS MONEY),1),'.00','')[WF],
REPLACE(CONVERT(VARCHAR,CAST([FF]AS MONEY),1),'.00','')[FF],
REPLACE(CONVERT(VARCHAR,CAST([ST]AS MONEY),1),'.00','')[ST] from (                  
Select col,col1,col2 from CTE)P
PIVOT(MAX(Col1) FOR COL IN([EQ],[WS],[SL],[TC],[WF],[FF],[ST]))P

Upvotes: 0

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

You will need first unpivot your data, and then pivot it again:

SELECT * FROM (/*your  current query here*/) t
UNPIVOT(v FOR col IN([B],[C],[BI])) u        
PIVOT (MAX(v) FOR PerilCode IN([EQ],[WS],[SL],[TC],[WF],[FF],[ST])) p

Upvotes: 2

pcofre
pcofre

Reputation: 4066

Probably not the most elegant, but this will do the trick by using the PIVOT syntax. Hope it works.

;with cteTable as 
(
-- Your query here
), cteTable2 as
(
select PerilCode, 'B' as SecondCol, B as Value from cteTable
union all
select PerilCode, 'C', C from cteTable
union all
select PerilCode, 'BI', BI from cteTable
)
SELECT SecondCol, [EQ],[WS],[SL],[TC],[WF],[FF],[ST]
FROM cteTable2
PIVOT
(
SUM(Value)
FOR PerilCode IN ([EQ],[WS],[SL],[TC],[WF],[FF],[ST])
) AS PivotTable;

Upvotes: 0

Related Questions