Hi i am trying get sub total for column and rows but my pivot query returns duplicate value. If write pivot with id column it gives perfect values but if write query with another column i am getting duplicate values.
select
case when convert(varchar,dist) is null then 'Grand Total'
else dist end district,
case
when dis is null then ''
else dis end Edist_name,
sum([P]) as pend,sum([A]) as App,sum([R]) as Rej,sum([P]+[A]+[R]) as Gr
from
(select distinct b.Edist_name as dis, convert(varchar,a.district) as dist,a.recpno,status, count(*) over(partition by status) as total
from loanee_greivance_details a,grievances d,Merged_DebtRelief_AfterVJC.dbo.Dist_master b
where a.bankcode=d.bankcode and a.branchcode=d.branchcode and a.LoanAcno=d.LoanAcno
and a.GroupId=d.GroupId and a.GIO=d.GIO and a.district=b.dist_code and a.RecpNo=d.RecpNo
and a.district=d.district) p
pivot (count(recpno) for status in ([P] ,[R],[A] )) as pvt group by pvt.dist,pvt.dis with rollup