CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Brave Soul
Brave Soul

Reputation: 3620

SQL Pivot Table not getting consolidated records

I want this output

enter image description here

I want to get unique with group by PortFolio Code but not getting the dessired output. I am getting following output. Please tell me where I have to use pivot or to get proper output

enter image description here

Here is my query

    select isnull('PortFolio Code: '+a.CPORTFOLIOCODE,'Total'),
(select sum(a.FOSAMT) where a.SZBUCKETCODE=1) as [Bucket :1],
(select sum(a.FOSAMT) where a.SZBUCKETCODE=2) as [Bucket :2],
(select sum(a.FOSAMT) where a.SZBUCKETCODE=3) as [Bucket :3] 
from dbo.COL_TRN_AGREEMENT a 
group by a.CPORTFOLIOCODE,a.SZBUCKETCODE 
with rollup

I tried to use pivot but failed to get through.

Upvotes: 2

Views: 120

Answers (1)

radar
radar

Reputation: 13425

removed with rollup and doing case based aggregation.

select isnull('PortFolio Code: '+a.CPORTFOLIOCODE,'Total'),
sum(case when a.SZBUCKETCODE =1  then a.FOSAMT else 0 end ) as [Bucket :1],
sum(case when a.SZBUCKETCODE =2  then a.FOSAMT else 0 end ) as [Bucket :2],
sum(case when a.SZBUCKETCODE =3  then a.FOSAMT else 0 end ) as [Bucket :3]
from dbo.COL_TRN_AGREEMENT a 
group by a.CPORTFOLIOCODE

Upvotes: 1

Related Questions